Excel 2007 : Count using multiple criteria and date range

**pkukk** · 04-08-2011, 04:54 PM

I'm trying to determine the formula to count the number of occurences of text from one column within a date range (today's date - last 10 days) from another column.

Attached is a sample report so I would need the number of CASH TRANS. received within the date range.

I have used this formula which references a specific cell, but I need to delete and sort the report so I would prefer incorporating the "today" function.

=COUNTIFS(Status!AB:AB,">="&Status!AB433,Status!X:X,"CASH TRANS.")

Any help is much appreciated!

**Cutter** · 04-08-2011, 05:06 PM

Try this:

In cell C2 to count CASH TRANS.

=COUNTIFS($A$2:$A$23,A2,$B$2:$B$23,">="&TODAY()-10,$B$2:$B$23,"<="&TODAY())

drag down to C3 to count FHA

**MarvinP** · 04-08-2011, 05:12 PM

Hi pkukk and welcome to the forum,

I know you are looking for a simple formula with =Today() in it but here is another way to use the power of Excel and get more than your thought your could.

Look at the attached with a Pivot Table and see if it might answer more than your original question. Play with Pivots a while with this easy example, making different combinations of Rows, Columns and Filters. You can also Group by Dates (as I've done) or sum, count or average numbers in the summation area.

**pkukk** · 04-08-2011, 05:22 PM

Thank you both! The formula works great and I will definitely explore using the Pivot Table...I think it opens a whole new level for me!

**Cutter** · 04-08-2011, 05:24 PM

You're welcome but I was thinking you don't need the last part of the formula I gave you because I doubt you'll have dates > than TODAY() in a column marked "Received".

Don't forget to mark your thread as SOLVED (click on FAQ link at top of page if you need directions).

Thanks for the "scales tap".