Excel: Cell counts based conditions

Question

I am again trapped in a situation where I can't figure how to do it. Basically, here is replica of the table I need to work on.

    A           B           C           D           E           F           G
1   Months      MON         TUE         WED         THU         FRI         SAT
2   Jan         Yes         No          N/A         Yes         Yes         MayBe
3   Feb         No          No          MayBe       Yes         N/A         No
4   Jan         No          Yes         MayBe       MayBe       Yes         No        
5   Apr         No          No          MayBe       Yes         N/A         No
6   May         No          Yes         MayBe       MayBe       Yes         No 
7   Jan         Yes         No          N/A         Yes         Yes         MayBe
8   Feb         Yes         Yes         N/A         N/A         No          MayBe
9   Apr         No          Yes         MayBe       MayBe       Yes         No
10              Yes         Yes         No          N/A         MayBe       MayBe

Now, on another tab I want to calculate all the Yes and MayBe in one cell for Jan and on another cell, I want to count all the No and N/A for Jan. Same way I want to count for Feb and Apr... all the months from column A.

so for Jan, I will have count of 12 which includes all the Yes and MayBe and 6 for all the No and N/A.

I hope this make sense.

Is there anyway to achieve this by formula only?

Thanks in advance.

Have you looked into `CountIf`\ `SumIf` functionality? Or the use of `Pivot Tables`? — Skip Intro, May 17 '13 at 16:15

score 2 · Accepted Answer · answered May 17 '13 at 16:28

Yes. You need to use array formulas. Obviously you'll need to correct the ranges. I have put my data arranged in the same way as you, just less columns and rows. When you input an array formula like this one, you need to type CTRL + SHIFT + ENTER in the cell for it to calculate properly.

=SUM(IF($A$2:$A$9="Jan",IF($B$2:$C$9="Yes", 1, 0), 0)) + 
    SUM(IF($A$2:$A$9="Jan",IF($B$2:$C$9="Maybe", 1, 0), 0))

This has worked on my local example.

score 1 · Answer 2 · answered May 20 '13 at 14:27

It might be easier with SUMPRODUCT, i.e.

=SUMPRODUCT((A2:A10="Jan")*ISNUMBER(MATCH(B2:G10,{"Yes","Maybe"},0)))

If you want the months to have multiple criteria then you can use MATCH for those too, e.g. for "Yes" or "Maybe" in "Feb" or "Apr"

=SUMPRODUCT(ISNUMBER(MATCH(A2:A10,{"Feb","Apr"},0)*MATCH(B2:G10,{"Yes","Maybe"},0))+0)

Excel: Cell counts based conditions

2 Answers2

Linked