COUNTIF: Data between 2 dates

Attempting to count data that lies b/t 2 dates AND that also meets specific criteria in another column


1) Column P counts days between 2 dates and returns a numerical value

Please Login or Register  to view this content.

2) This counts how many cells from column P note that the 1st date and 2nd date are between 3 and 7 days of each other

Please Login or Register  to view this content.

3) This counts how many cells in column L contain the words Clean or Short

Please Login or Register  to view this content.

}

4) What I want is to be able to count how many dates that fall between 4 and 7 days of each other also have the words Clean or Short in the L column.

I figured out the formula that works when dates are less than or equal to 3 days apart

Please Login or Register  to view this content.

But when I substitute

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

it doesnt work.

my best attempts:

1)

Please Login or Register  to view this content.

(Returns 942... way off)

2)

Please Login or Register  to view this content.

(Returns 1... way off)

Ideally I would like to understand how to properly work COUNTIF formulas (i suppose its a syntax or order problem) so I dont have to spend 2 hours trying to figure it all out every time i hit a speed bump.

I am using Excel 97

EDITS: Trying to edit to Forum suggestions and rules. also included an example of what I am trying to do.

A Pivot Table would probably be a good choice for this.
However for a formula solution that uses multiple criteria, see this video tutorial on the use of SUMPRODUCT

Also, see these pages: Summarize Data with SumProduct

It would be easier if you uploaded a sample workbook (I don't think you need the intermediate columns) but based on the information you gave, try this

=SUMPRODUCT(--($P$2:$P$999<=14),-- ($P$2:$P$999 >=7), ($L$2:$L$999="clean")+ ($L$2:$L$999="short"))

Does that work?

I modifed my SUMPRODUCT to reference two cells (min and max days for each set). I modified your column H into two columns for min and max days for each set See attachment.

So in M4 for example, the formula is
=SUMPRODUCT(--($F$2:$F$999<=I4),-- ($F$2:$F$999 >=H4), ($D$2:$D$999="clean")+ ($D$2:$D$999="short"))
dragged down.

Does that work for you?

Chemist, this does indeed work. A little different than I expected, but also something that didnt even cross my mind. Good thinking. Thinking out side of the cell. Perhaps one more question: Why sumproduct rather than countif. I imagine it is faster/more efficient for some reason?

Otherwise: solved

Edit: Premature SOLVED. I have no idea what is going on, but when I plug that formula into my spreadsheet, changing the cell references, I get the #VALUE! error.

Thoughts?

Something happened. I have no idea what is going on, but when I plug that formula into my spreadsheet, changing the cell references, I get the #VALUE! error.

All i did was change

=SUMPRODUCT(--($F$2:$F$999<=I4),-- ($F$2:$F$999 >=H4), ($D$2:$D$999="clean")+ ($D$2:$D$999="short"))

to

=SUMPRODUCT(--($P$58:$P$999<=BK24),-- ($P$58:$P$999 >=BJ24), ($L$58:$L$999="clean")+ ($L$58:$L$999="short"))

No idea what is going on here. Thoughts?

* Does it matter if some cells are blank in the Refrence column P58:P999 or L58:L999?


***Never mind, found out that there were some cells in the reference column (P) that were not numbers. I deleted those cells' values and it now works. Thank you.