Countifs scenario, including "<= Date"

[Melvinrobb]

Hi All
I am attempting a countif function based on the Product, Category and Date.
In the attached file (assuming it attached, as the regular attachment option was not working, and I had to attempt another way) I have highlighted the only two rows where the function is not working correctly, and have specified what the function should be returning.
In theory, every A-1 / Category 1 combination should increase the field by 1, resulting every A-1 / Category 1 combination having a unique number.
I can't figure out why those two are not working correctly.
Any ideas?
Thanks.

EDIT: NOTE: If i go into cell C37 and hit enter, the formula works. Something about that cell has issues for the formula, and since this will be a report someone else will be copying over routinely, I want a more long term solution (rather than manually searching for the errors, and fixing those cells).

[AlKey]

Try this

Please Login or Register  to view this content.

[Sam Capricci]

usually when you have to click in a cell and hit enter to get it to work that means your calculations option under the options is set to manual instead of automatic.
When I open your attachment it is set to automatic but I have two other sheets open and I don't know if it is defaulting to that.
did you check the settings?

[Melvinrobb]

Sambo - No, it is definitely on Automatic. It seems to be just an excel glitch with the countifs formula.
AlKey - Yes, that works. I would prefer to use countifs rather than sumproduct, but i guess this works fine.

If anyone has any idea what the issue with the countifs is (assuming it is not a glitch) I'd love to know.

[CK76]

Hmm, interesting issue.

Looks like the culprit is the same row's [Date].

If you use...
=COUNTIF(C37,"<="&C37)
It will return 0. Though when you test =C37<=C37 it returns true.

I suspect string concatenation is causing issue. But not 100% on that.

Try using following formula instead.
=SUMPRODUCT(([Date]<=[@Date])*([Product]="A-1")*([Category]="Category 1"))