# SUMPRODUCT HELP

#### jmathew

##### Board Regular
I am looking for some help with a formula. I have:
=SUMPRODUCT(--(SHEET1!\$M3:\$M5001="KNEE"),--(SHEET2!\$Q3:\$Q5001="KNEE"))
I am trying to count the number of occurances of Knee on one sheet and also count the occurances on another sheet and add them together. The above formula does not return a result. Is there something wrong with the formula? Any help is greatly appreciated. Thank you!

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
That works for me. But note that you are testing to see if "KNEE" is in the same row on both sheets. If you simply want the # of times "KNEE" appears on both sheets then just use:
=COUNTIF(Sheet1!\$M4:\$M5002,"KNEE")+COUNTIF(Sheet2!\$Q4:\$Q5002,"KNEE")

This is one way to accomplish your goal; I'm sure there's a shorter method:

=COUNTIF(Sheet1!\$M\$3:\$M\$5001,"KNEE")+COUNTIF(Sheet2!\$M\$3:\$M\$5001,"KNEE")

Brian

I made a boo boo
=COUNTIF(Sheet1!\$M\$3:\$M\$5001,"KNEE")+COUNTIF(Sheet2!\$Q\$3:\$Q\$5001,"KNEE")

ok two boo boo's
=COUNTIF(Sheet1!\$M3:\$M5001,"KNEE")+COUNTIF(Sheet2!\$M3:\$M5001,"KNEE")

I tried the suggested formulas and it doesn't seem to work. Is it b/c I am comparing row Q in one sheet and row M in the other?

Well, it would help if I had noted that your formula only locked columns and not rows since I copied it down and then edited to create the example I posted -- where the row ref is off by one. Should have been:
=COUNTIF(Sheet1!\$M3:\$M5001,"KNEE")+COUNTIF(Sheet2!\$Q3:\$Q5001,"KNEE")

And this works fine for me. (And it is not case sensitive.)

I did notice that and it still doesn't work for me. I don't know what is wrong!!!!

So break it into pieces and test the components. I.e. put =COUNTIF(Sheet1!\$M3:\$M5001,"KNEE") into a cell. Does this yield what you expect? Then put =COUNTIF(Sheet2!\$Q3:\$Q5001,"KNEE") into another cell. Does this yield what you expect?

{Edit}Um, we are looking just for those cells that just have "KNEE" and nothing else in them, right?{EndEdit}

maybe replace "KNEE", with "*KNEE*" if your cells contain more than simply the word.

Could you post an example of your data?

I got it. It did need the * after Knee. Thank you!!!!!!!!!!!!!

Replies
20
Views
752
Replies
7
Views
246
Replies
2
Views
608
Replies
1
Views
169
Replies
1
Views
280

1,203,078
Messages
6,053,403
Members
444,662
Latest member
AaronPMH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back