Baffled by SumIf issue.

=SUMIF($D$1:$D$2,$G$1:$G$2,$E$1:$E$2) isnt the correct format for sumif
SUMIF(range, criteria, [sum_range])
from help files
=SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."
you cant have a range for the criteria

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Agree w/Martin, but from what you've written (and his first reply is still correct) you can just replace =SUMIF(B2:B5, "John", C2:C5) "John" with the cell reference you have your distinct list - of course one cell at a time.
So if your criteria is in, say cell d2 you point the formula to... =SUMIF(B2:B5, D2 , C2:C5) and just go from there down the list, so it might look like =SUMIF($B$2:$B$1000, D2, $C$2:$C$1000)

Originally Posted by MLocke

So it looks like the SumIF is reading both as identical for the purposes of the shorter item, but not the longer one. How the heck do I avoid this?

The problem is with the asterisks - SUMIF thinks that these are "wildcards" not literal asterisks so it identifies different items as the same

Use a SUBSTITUTE function in the criterion so instead of this.....

=SUMIF($D$1:$D$2,$G$1,$E$1:$E$2)

....you can use this.....

=SUMIF($D$1:$D$2,SUBSTITUTE($G1,"*","~*"),$E$1:$E$2)

That will replace any asterisk in the criterion value with "~*" whereby the ~ [tilde] tells excel to treat the asterisk as a literal asterisk. It works even if there are no *s present.

....or you can use SUMPRODUCT instead of SUMIF like this

=SUMPRODUCT(($D$1:$D$2=$G1)+0,$E$1:$E$2)

SUMPRODUCT won't recognise * as a wildcard so the problem doesn't occur with that function