Sumif or Sumproduct with Multiple Rows & Columns

This question is a bit different from any other that I could find. For important reasons I have a spreadsheet that has a setup similar to the example below. I would like, for example, to use a formula that would sum the total "Criteria 2" for those columns labeled with "A". I would expect the total to be equal to 40.00, but I cannot figure out a way to have a formula sum all criteria 2s that match a description found in multiple columns (there is a sub-header below the column headings that I need to leave intact - otherwise I would just sum the data into one column). Thanks in advance!

The column headers are A, B, C, A, D, E, F and are over the 10.00's - cannot figure out how to make it show up correctly in this post. I've also attached a sample workbook.

A B C A D E F
Criteria 1 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 2 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 3 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 1 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 5 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 2 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 7 10.00 10.00 10.00 10.00 10.00 10.00 10.00

Originally Posted by NBVC

Try:

=SUMPRODUCT(($C$2:$C$8="Criteria 2"))*($D$1:$J$1="A"),$D$2:$J$8)

note the criteria can refer to cells containing the text.

Awesome... one too many ) in the middle there, but does exactly what I was asking. Thanks so much.

NBVC you are the man, your formula just saved me a ton of work

Austin, NBVC is still on the site, but I doubt he still reads three year old threads.
Maybe send him a PM?

Greetings Forum,

I have a similar dilemma as to the above gentleman. I am trying to add only column PA. I was trying to insert a sumproduct but i kept getting an error. I have attached a picture to show an example. I would like to be able to drag the formula down for all products.
Any help is greatly appreciated. Thank you


PA PA TX TN J1 76 KS Req PA TX TN J1 76 KS Req PA TX TN J1 76 KS Req PA TX TN J1 76 KS Req PA TX TN J1 76 KS Req
Product A 1 2 3 4 5
Product B
Product C
Product D




Excel Example.PNG