Share via

Combining sumif and vlookup match

Anonymous
2014-03-26T14:43:54+00:00

Good morning All,

I have a table below and want to summarize the total Pay, Vac and OT by person. I want to be able to combine sumif and vlookup match. The reason is because the coloum is always change. For ex: "Pay" could be on column D next time so I just can not use sumif or sumifs function.

         A                B               C              D

Pay Vac OT
Sam 100 5 10
Sam 10 5 10
Sam 10 5 10
Jim 150 5 10
Jim 10 5 10
John 200 10 10

Basically, this is what I need to do below.

Sam: =sumif(vlookup("sam", A2:D7,(match("pay",A1:A4,0)))

Jim: =sumif(vlookup("Jim", A2:D7,(match("pay",A1:A4,0)))

Thank you

John

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2014-03-26T15:53:36+00:00

You can SUMIF() using column A as the criteria and block the hours with INDEX() choosing the appropriate column with MATCH().

The standard formula in G2 is,

=SUMIF($A:$A,$F2,INDEX($B:$D,,MATCH(G$1,$B$1:$D$1,0)))

This can be copied both right and down.

If you are interested, the names in F2:F999 were collected with an array formula in F2.

=IFERROR(INDEX($A$2:$A$999,MATCH(0, IF(LEN($A$2:$A$999),COUNTIF(F$1:F1,$A$2:$A$999),1),0)),"")

This requires Ctrl+Shift+Enter rather than simply Enter. Once entered correctly it can be filled down to collect a unique list of the names in column A.

Hyperlink Description
SUMIF function Adds the cells specified by a given criteria
INDEX function Uses an index to choose a value from a reference or array
MATCH function Looks up values in a reference or array
IFERROR function Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula

Edit: the array formula started in F2 not G2.

Was this answer helpful?

20+ people found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2014-03-26T15:33:10+00:00

Try a formula like this:

=SUMIF($A:$A,"sam",INDIRECT(ADDRESS(1,MATCH("pay",$A$1:$D$1,0)) & ":" & ADDRESS(COUNTA(A:A),MATCH("pay",$A$1:$D$1,0))))

The "sam" and "pay" portions could be cell addresses containing those words instead of the literals.

How the INDIRECT() pulls it all together:

the MATCH("pay",$A$1:$D$1,0) part I think you already understand, it's just getting the column number that the word pay is in on row 1.

Combine that with ADDRESS(1, MATCH()) and it gives you the address of the first cell in the column with the word "pay" in it:  $B$1

The second ADDRESS(0 works in similar fashion:  to get the row number for the address needed for the second part of the formula, we count the number of entries in column A.  This does assume that the list is continuous with no empty cells within filled cells in column A.  Then we get the column number again for where "pay" is, so that is effectively ADDRESS(7,2) or $B$7.

They are both combined into a range type reference with the & ":" & part to give us a string literal of $B$1:$B$7  which is where you want to get your SUMIF() from.  The INDIRECT() says "take this string literal and use it as an address).

I think this will get the job done for you.

Was this answer helpful?

3 people found this answer helpful.
0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-05-10T07:43:55+00:00

    Hi,

    MS Excel provides the "subtotal" function under data - outline.

    For it to work, put a header for your column where the names appear. I have named it "name".

    Function is illustrated below. It gives totals for each name.  You need to click the boxes under "add subtotal to" wherever you want subtotals calculated.

    Grand total and Sub total are visible separately in newly formed sheets 1 and 2.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2014-03-26T22:38:33+00:00

    Thanks for the feedback - glad we were able to help.  I think Jeeped's solution is probably the better of the two - I believe it will function faster than all of the INDIRECT()s used in the one I offered.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-03-26T18:10:36+00:00

    Thank you so much for the answer for both of you. It works perfectly. You guys are awesome.

    Was this answer helpful?

    0 comments