sumif or index formula help

[ben803]

Hello, i've attached a worksheet that helps explain my issue with either a sumif or index formula.

I basically would like a formula that will help me search for a particular name and give me that dollar amount from another column but to exclude another column that is associated with that particular name as well.

i might not be explaining myself correctly so hopefully viewing the excel file that i've attached can help better explain my issue.

please, any help would be appreciated. Thank you.

[tigeravatar]

ben803,

In cell B25, try the following formula:
=SUMPRODUCT(--($A$5:$A$16=A25),--($B$5:$B$16<>$A$31),$C$5:$C$16)

and then copy down

[ConneXionLost]

How about:

Please Login or Register  to view this content.

Cheers,

[ben803]

wow, you guys are great. Thank you so much. I really really appreciate this. It works.

Thank you again for your time.

[ben803]

oops, one more question. Same issue, but if i wanted to do a countif formula, is that possible?

[ben803]

was looking for the number "2" as my answer for a countif.

[tigeravatar]

If you mean you just want to count the number of occurrences of matching criteria instead of summing the amount of matching occurrences, you would just leave out the last argument in the sumproduct formula (C5:C16) so that it looks like this:
=SUMPRODUCT(--($A$5:$A$16=A25),--($B$5:$B$16<>$A$31))

[ConneXionLost]

Then try:

Please Login or Register  to view this content.

Cheers,

[ben803]

thank you again for your time. it definitely works.