Help with a Vlookup and SUMIF

[Lucero]

Hi,
I need help with a formula that looks for a name in the "Name" column (which contains the same name multiple times) and then adds its value in the column "Expense Program" and column "Expense Admin".

Please see attachment for detail info.

Thank you very much!!!

300

[Rick Rothstein]

Hi,
I need help with a formula that looks for a name in the "Name" column (which contains the same name multiple times) and then adds its value in the column "Expense Program" and column "Expense Admin".


Does this formula do what you want...

=SUMIF(A$2:A$42,F2,B$2:B$42)+SUMIF(A$2:A$42,F2,C$2 :C$42)

[Rick Rothstein]

Does this formula do what you want...

=SUMIF(A$2:A$42,F2,B$2:B$42)+SUMIF(A$2:A$42,F2,C$2 :C$42)

Here is another formula that I think works the way you want...

=SUMPRODUCT((A$2:A$42=F9)*B$2:C$42)

[Lucero]

Both of them worked! Thank you!!

oh and how can I add an additional criteria to that formula? I added it in columns D & E "S/NS" and "SUP"
I manually added A & B so that you can see the results needed.

Thank you!
301

[Rick Rothstein]

Both of them worked! Thank you!!

oh and how can I add an additional criteria to that formula? I added it in columns D & E "S/NS" and "SUP"
I manually added A & B so that you can see the results needed.


Use these formulas in the indicated cells and then copy them down...


H2: =SUMPRODUCT((A$2:A$42=G3)*(D$2:D$42="NS")*(E$2:E$42="Y")*B$2:C$42)

I2: =SUMPRODUCT((A$2:A$42=G3)*(D$2:D$42="NS")*(E$2:E$42="N")*B$2:C$42)

J2: =SUMPRODUCT((A$2:A$42=G3)*(D$2:D$42="S")*(E$2:E$42="Y")*B$2:C$42)

K2: =SUMPRODUCT((A$2:A$42=G3)*(D$2:D$42="S")*(E$2:E$42="N")*B$2:C$42)

Notice this is the same formula repeated with the "NS" and "Y" from the first formul changed to match the two conditions indicated in the headers (H2:K2).