Hi, this is my first post so I hope someone can help.
My dataset:
<TBODY>
</TBODY>
I am using Excel 2003 formula:
=(SUMPRODUCT((B24:B100<>"")/COUNTIF(B24:B100,B24:B100&"")))
to give me the number of available sets = 5 (A0, A1, A2, B1 & C2)
and
=SUMPRODUCT(--(LEFT(A24:A100,5)="Apple"),--(LEFT(B24:B100,1)="A"),--ISNUMBER(FIND("Y",C24:C100)))
to give me the number of sets containing "Apple", beginning with "A" and available "Y" = 4
The output I actually need is 2, that is the number of "A" sets that are available "Y" and contain Apples = 2 (A0 & A1)
Does anyone know how to do this? Any help would be appreciated
My dataset:
Fruit | Item Set | Available |
Banana | A1 | Y |
Apple | A2 | N |
Banana | A2 | N |
Kiwi | A2 | N |
Apple | A1 | Y |
Peach | B1 | Y |
Apple | A1 | Y |
Apple | A1 | Y |
Apple | A0 | Y |
Banana | C2 | N |
<TBODY>
</TBODY>
I am using Excel 2003 formula:
=(SUMPRODUCT((B24:B100<>"")/COUNTIF(B24:B100,B24:B100&"")))
to give me the number of available sets = 5 (A0, A1, A2, B1 & C2)
and
=SUMPRODUCT(--(LEFT(A24:A100,5)="Apple"),--(LEFT(B24:B100,1)="A"),--ISNUMBER(FIND("Y",C24:C100)))
to give me the number of sets containing "Apple", beginning with "A" and available "Y" = 4
The output I actually need is 2, that is the number of "A" sets that are available "Y" and contain Apples = 2 (A0 & A1)
Does anyone know how to do this? Any help would be appreciated