Count occurences of text in range only when Yes appears in other c

  • Thread starter Thread starter eliyahuz
  • Start date Start date
E

eliyahuz

I have the following data
J K P
2 APRON Sony Camcorder Yes
3 Vivitar Digital Camera
4 APRON iHome iPod Dock Yes
5 APRON Vibrating Alarm Clock Yes
6 CAMERA Yes
7 camera Yes
8 Girl's Timex Watch

I want to count occurences of APRON in range (actually 7 columns like this
by 50 rows) ONLY if Yes appears in Column P.

I've tried COUNTIF, SUMIF, SUMPRODUCT and others but I can't get it right. I
either end up with errors (#VALUE) or the wrong sum. Any ideas?
 
Have you tried this:

=SUMPRODUCT((P2:P8="Yes")*(ISNUMBER(SEARCH("APRON",J2:J8))))

?

Hope this helps.

Pete
 
Your example wasn't very clear - ISNUMBER and SEARCH are checking to
see if Apron appears anywhere in the cells J2:J8. Your later post
shows the Delivery status in column Q, and it's on a different sheet,
so try this:

=SUMPRODUCT((Sheet1!Q2:Q8="Yes")*(Sheet1!J2:J8="APRON"))

where Sheet1 is the name of the sheet where your data is.

Hope this helps.

Pete
 
I tried it again and replaced your "sheet1" with the sheet's name and I ended
up with a #NAME? error. Did I do something wrong?
 
If you have a space in the sheet name then you need to put apostrophes
around it, like so:

=SUMPRODUCT(('Sheet name'!Q2:Q8="Yes")*('Sheet name'!J2:J8="APRON"))

Hope this helps.

Pete
 
Thanks Pete! That did it!

Pete_UK said:
If you have a space in the sheet name then you need to put apostrophes
around it, like so:

=SUMPRODUCT(('Sheet name'!Q2:Q8="Yes")*('Sheet name'!J2:J8="APRON"))

Hope this helps.

Pete



.
Click to expand...
 
Back
Top