Require Help with Counting based on multiple Criteria (array)

Originally Posted by oOarthurOo

I've attached a worksheet that more clearly explains the problem. I have one sheet with records in a line list format. A second sheet that is for summary purposes.

A record may or may not contain the word "Critical" in one column, but it will certainly have a date and a site reference.Each incident gets its own record, so one site may have several records for a single day, each containing the word critical.

The summary sheet has the last 30 days in a row across the top. The first column is the name of each community. I want to count the number of critical incidents, for each site, for each day, and display them. Something like this:

Site****Apr15 | Apr14 | Apr13 | Apr12 | Apr11 | Apr 10
Alpha** 0 | 0 | 1 | 0 | 2 | 3
Beta*** 1 | 2 | 0 | 0 | 0 | 0


To solve this problem I have tried using sumproduct, sumproduct in an array, sum(if(if formulas, and lately, vlookups, though I can't imagine how those might work.

Hoping someone can help my break through this "writer's block".

Are you using excel 2007?

If so, you can put this in B3:

Please Login or Register  to view this content.

Then expand it out to all the cells in your grid. This (correctly) shows that you have a critical for Beta on Apr 15th also.

Originally Posted by contaminated

ry in B3
=SUMPRODUCT(($A3=Data!$A$2:$A$13)*(B$2=Data!$C$2:$C$13))
or
=SUM(INDEX(($A3=Data!$A$2:$A$13)*(B$2=Data!$C$2:$C$13),0))

independent from your xl version

copy down then accross

He only wants ones with "Critical" in Column D. Your formulas can be easily modified to include this, so I'll let you...

=SUMPRODUCT(($A3=Data!$A$2:$A$13)*(B$2=Data!$C$2:$C$13)*(Data!$D$2:$D$13="Critical"))
independent from your xl version

Thanks.. this is the one that did the trick. On the plus side my first thought to use sumproduct was correct. The downside was I'm not quite that good with it yet. This should help.

One mistake I was using was entering the entire table as a range. Then when I tried entering it as an array formula. What I didn't expect is that by selecting distinct non-overlapping ranges, the formula can still tell if the three conditions are met. I suppose that is because it analyzes by row only, if I had to guess.