Counting multple criteria in multiple cells.

**JapanDave** · 05-07-2009, 05:38 AM

I have been a lurker for a while, but this is my first post .
I have not a hope of explaining this without a pic, so I hope this helps.

In the attachment at the bottom of the page I want to count the symbols in the blue selected area when they are apear into the cells in the red selected area. The catch is that I want to treat the each of the "RED", "BLUE", "GREEN" as a single colum so to speak and have a value "1" or "0" apear in the back circled area.

Eg, For MrB there is nothing in the "RED" column (B7:D7) so that gives a value of zero in cell cell "P7". In the "BLUE" column (E7:G7) there is a circle which will give a value of "1" in cell "Q7". In the "GREEN" column (H7:I7) there is a double circle and a square symbol and that will return a value of "1".

I hope this makes sense. I have tried to do this with counta, but that will also count cells when a space bar is used and that makes it difficult to make sure that I am getting the right info. I also tried VLOOKUP and COUTIF but this is beyond my level. I hope someone can help.Cherrs.

**zbor** · 05-07-2009, 06:14 AM

can you uplaod an workbook example?

**JapanDave** · 05-07-2009, 09:14 PM

Originally Posted by zbor

can you uplaod an workbook example?

I will try, give me a few hours.

**Clark** · 05-07-2009, 10:56 PM

Hi NipponDave,

With regards to your pic, in cell P 6, you could put the following formula:

=IF(AND(ISBLANK(B6)=TRUE,ISBLANK(C6)=TRUE,ISBLANK(D6)=TRUE)=TRUE,0,1)

**JapanDave** · 05-11-2009, 09:40 PM

Originally Posted by Clark

Hi NipponDave,

With regards to your pic, in cell P 6, you could put the following formula:

=IF(AND(ISBLANK(B6)=TRUE,ISBLANK(C6)=TRUE,ISBLANK(D6)=TRUE)=TRUE,0,1)

Thanks so much for formula Clark! I have made a formula similar to this one, however the problem I encountered was that when people(My staff use this form) select the symbol and press the space bar these formulas register the cell as text. There is no way to garrentee that someone won't use the space bar so I was looking for a formula that actually counted the symbols.

Zbor, sorry being so slow but I was snowed with work.

Here is the sample book. BTW I am on 2007, but I uploaded the book in 2003 format.