# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Sumif or Sumproduct with Multiple Rows & Columns

## vulches

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

----------


## 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.

----------


## vulches

> 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

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




Good catch.  Just testing  :Wink:

----------


## austin123

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

----------


## arthurbr

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

----------


## lqorri

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

----------


## FDibbins

lqorri welcome to the forum  :Smilie: 

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

Aslo, Pictures are of little value.  Honestly, no one wants to re-type your data to try and solve your issue.  Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc.  Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images.  Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post, 
click advanced (next to quick post), 
scroll down until you see "manage file", 
click that and select "add files" (top right corner). 
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window. 
You can then close the window to return to the new post screen.

----------

