Formula that will count the # of rows that contain a specific text and a specific month?

[OodaLoop]

I'm trying to come up with a count formula to meet my needs.


I want the formula to count each row in a sheet that meets two requirements.

1. A cell in a certain column contains a specific text.
2. A cell in a certain column falls within a specific month, the cell is currently in the mm/dd/yyyy format.

Here is an example of how the table looks:


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Title[/TD]
[TD]Level[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]Water[/TD]
[TD]Critical[/TD]
[TD]1/2/13[/TD]
[/TR]
[TR]
[TD]Power[/TD]
[TD]High[/TD]
[TD]1/15/13[/TD]
[/TR]
[TR]
[TD]Power[/TD]
[TD]High[/TD]
[TD]1/15/13[/TD]
[/TR]
[TR]
[TD]Filter[/TD]
[TD]Low[/TD]
[TD]1/30/13[/TD]
[/TR]
[TR]
[TD]Heat[/TD]
[TD]High[/TD]
[TD]2/5/13[/TD]
[/TR]
[TR]
[TD]Ducts[/TD]
[TD]Low[/TD]
[TD]2/15/13[/TD]
[/TR]
[TR]
[TD]Lock[/TD]
[TD]Low[/TD]
[TD]2/15/13[/TD]
[/TR]
[TR]
[TD]Window[/TD]
[TD]Low[/TD]
[TD]2/15/13[/TD]
[/TR]
</tbody>[/TABLE]




I have this formula for counting how many rows fall in a specific month:

=SUMPRODUCT(--(C$2:C$1048576<>""),--(MONTH(C$2:C$1048576)=1))

And I have this formula for counting how many rows contain a specific text (Level)

=COUNTIF(B2:B1048576, "High")

I need to combine them somehow. Not sure if it is possible to combine these two exact methods but I need something that lets me count the rows that are of a specific month and of a specific level.


Thanks for any help everyone.

 

[jim may]

What would be the Answer in this case with the combined formula?

 

[Rick Rothstein]

I think you are probably looking for this formula...

=SUMPRODUCT(--(B2:B1048576="High"),--(MONTH(C2:C1048576)=1))

but I would recommend reducing the 1048576 row numbers to a lower value that will still handle your current and anticipated future needs (the formula will be more efficient if you do so).

 

[jim may]

In the mean time...

[TABLE="width: 54"]
<tbody>[TR]
[TD="class: xl63, width: 72, bgcolor: transparent"]

[TABLE="width: 755"]
<tbody>[TR]
[TD]=SUMPRODUCT(--(C$2:C$1048576<>"")*(--(MONTH(C$2:C$1048576)=1))*(--($B$2:$B$1048576="High")))
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[circledchicken]

Hi and welcome to the forum,

Perhaps add a third array in your SUMPRODUCT like this:

=SUMPRODUCT(
    --(C2:C1048576 <> ""),
    --(MONTH(C2:C1048576) = 1),
    --(B2:B1048576 = "High"))

I think the first array in the SUMPRODUCT is still useful as otherwise an empty cell will be considered as January.

As per Rick's post, you should probably use a smart table or dynamic named ranges:
Working with Tables in Excel 2013, 2010 and 2007
Excel Names -- Excel Named Ranges

Alternatively you could have a seperate column that identifies the MONTH and then use the faster COUNTIFS function.

 

[Rick Rothstein]

Perhaps add a third array in your SUMPRODUCT like this:

=SUMPRODUCT(
    --(C2:C1048576 <> ""),
    --(MONTH(C2:C1048576) = 1),
    --(B2:B1048576 = "High"))

I think the first array in the SUMPRODUCT is still useful as otherwise an empty cell will be considered as January.

Good point (assuming the data really does have blank cells within it).

 

[OodaLoop]

Oh using a table to do it was a great idea. I don't know why I didn't think of that! Trying to figure out now, how to group by month... would that be even possible?

 

[circledchicken]

Try using a PivotTable - that will allow you an easy way to group by month as well as giving you the count:
Excel Pivot Table Tutorial -- Grouping Data