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

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.png

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

Try this:

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

It's not a good idea, though, to use such large ranges with SP - can you make your ranges shorter?

Hope this helps.

Pete

Thanks, but that resulted in a #Value error.

Can you elaborate on why it is a bad idea to use such large ranges? I can probably reduce it some but this spreadsheet will be pretty big.

Well, that's a million+ cells that the formula has to work on in each column, and then build up an intermediate array of values which will also be a million+, so it will be very sluggish with such long ranges, and if you only have, say, 200k rows of data (which is still a big spreadsheet), then you are getting Excel to do 5 times 4 times more calculations than it needs to. I would suggest using another column to derive the month, and then you can use COUNTIFS - this function is intelligent enough not to calculate beyond the used range, so it will be much faster and you can use full-column references without concern for the speed.

Hope this helps.

Pete