Count text occurences in range when criteria in other column is me

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

eliyahuz

I have a spreadsheet with data like this:
J K L Q
1 GIFT #1 GIFT #2 GIFT #3 DELIVER
2 APRON Camcorder Clock Yes
3 Stereo Apron Yes
4 Camera
6 APRON Yes
7 APRON Clock
8 Clock Yes

I want to count occurences of "Apron" in range J2:J8 ONLY if "Yes" occurs in
Column Q of that record (row). The total will be in a different sheet of teh
same workbook (The sheets are named.)
Any suggestions how to accomplish this?
I tried COUNTIF, SUMIF, SUMPRODUCT. Nothing gave the proper result. I either
received an error (#NAME or #VALUE) ot the wrong result (0).
Any suggestions are appreciated. Thanks.
 
Check out your earlier post. If the formula is in a different sheet
then you will need to put the sheet name before the ranges, separated
by a !, i.e.:

Sheet1!J2:J8

The other difference is that now your Delivery column is Q instead of
P.

Pete
 
It is supposed to be Q. I reposted the question because when I posted it
initially it didn't seem like it went through. The problem remains though
that the results show 0. It should be 3. I'm obviously missing something.
 
Try the below formula with cell R1 = "Apron"
=SUMPRODUCT(--(J1:J10=R1)+(K1:K10=R1)+(L1:L10=R1),--(Q1:Q10="Yes"))

If this post helps click Yes
 
Back
Top