SumProdect with Text & Ints

[jo15765]

I had a thread here:
http://www.excelforum.com/excel-form...ml#post4256719

and both
@oeldere
@bebo021999

Helped me out greatly on that one! But now that I have gotten the actual workbook there are a few text values input (they are not blank like originally thought), so the SUMPRODUCT() formula throws an error.

So same criteria....and desired output...
All worksheet holds the data
C2:EF2 is the header row where the text woudl be "Shirts Ordered This Week"
MATCH(A2) student name text to match-up on All worksheet
ALL!$A:$A houses the entire student list (so match A2 from above)

if someone wouldGarbageWorkbook.xlsx again be so kind to assist.

[Jonmo1]

Try

=SUMIF(All!$C$2:$R$2,$I$1,INDEX(All!$C$3:$R$14,MATCH(A2,All!$A$3:$A$14,0),0))

[Ron Coderre]

Using your posted workbook...
this formula returns the total "shirts ordered this week" for the referenced name

Please Login or Register  to view this content.

Copy that formula down through L13.

EDITED TO INCLUDE THIS VARIATION:

Please Login or Register  to view this content.

Is that something you can work with?

[bebo021999]

Use this instead:

=SUM((All!$A$3:$A$14=Totals!$A2)*(All!$C$2:$R$2=Totals!I$1)*IFERROR(--All!$C$3:$R$14,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[bebo021999]

Thanks Ron, a magic sumproduct!

[FDibbins]

I had worked on this (and got called away), so I am posting my version anyway (pretty much the same as Ron's...
=SUMPRODUCT((All!$A$3:$A$14=Totals!$A2)*(All!$C$2:$R$2=Totals!I$1),All!$C$3:$R$14)