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

[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?

 

[Pete_UK]

Have you tried this:

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

?

Hope this helps.

Pete

 

[Mike H]

Hi,

Sumproduct will work fine

=SUMPRODUCT((J1:J16="Apron")*(P116="Yes"))

Mike

 

[eliyahuz]

Thanks. I tried that but the result was 0. What is ISNUMBER?

 

[eliyahuz]

I get a o result. It should be 3.

Hi,

Sumproduct will work fine

=SUMPRODUCT((J1:J16="Apron")*(P116="Yes"))

Mike

 

[Pete_UK]

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

 

[eliyahuz]

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?

 

[Pete_UK]

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

 

[eliyahuz]

Thanks Pete! That did it!

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



.

 

[Pete_UK]

You're welcome - I'm glad we finally got there.

Pete