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

Pete

On Oct 26, 4:30*pm, eliyahuz
wrote:
Thanks Pete! That did it!



"Pete_UK" wrote:
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

On Oct 25, 9:23 pm, eliyahuz
wrote:
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" wrote:
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="AP RON"))

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

Hope this helps.

Pete

On Oct 25, 8:13 pm, eliyahuz
wrote:
Thanks. I tried that but the result was 0. What is ISNUMBER?

"Pete_UK" wrote:
Have you tried this:

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

?

Hope this helps.

Pete

On Oct 25, 6:17 pm, eliyahuz
wrote:
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? * * * *

.- Hide quoted text -

- Show quoted text -

.- Hide quoted text -

- Show quoted text -

.- Hide quoted text -

- Show quoted text -