SUMPRODUCT HELP

[jmathew]

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!

 

[Greg Truby]

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")

 

[BrianM]

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")

 

[jmathew]

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?

 

[Greg Truby]

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

 

[jmathew]

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

 

[Greg Truby]

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}

 

[IML]

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

 

[BrianM]

Could you post an example of your data?

 

[jmathew]

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