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

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

Pete_UK

Have you tried this:

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

?

Hope this helps.

Pete
 
P

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
 
E

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

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
 
E

eliyahuz

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



.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top