Using COUNTIFS between dates when one more condition - can they be in one formula or not?

[RogerRangeRover]

Hi!

If I have a the following formula to count the cell A1 only if it has a date which is equal or higher than the date in cell B1, and equal or lower to the date in cell, as follows:

COUNTIFS(A1;">="&B1;A1;"<="&B2)

Now I would only want to count as per this condition if the cell A2 has a value "Z".

Can you do this by using one COUNTIFS formula alone? If I use COUNTIFS()+COUNTIFS() the result would be 2: one count for the value Z and one for the date, although the aim is to have one count when both the date meets the condition and the value is Z.

It seems you canīt for example do this

COUNTIFS(A2;"Z";A1;">="&B1;A1;"<="&B2) or
COUNTIFS(A2;"Z");COUNTIFS(A1;">="&B1;A1;"<="&B2) or
COUNTIFS(A2;"Z")(COUNTIFS(A1;">="&B1;A1;"<="&B2))

For this one I would really want to avoid using a helper column.

Many thanks for any ideas!

[dilipandey]

Hi Roger,

Please upload a sample workbook with sample scenario.. thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

[RogerRangeRover]

Hi Roger,

Please upload a sample workbook with sample scenario.. thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

Please see attached a sample scenario. Where I have the problem is to use the COUNTIFS between dates plus one conditions from another Sheet.

[Kevin UK]

Hi RogerRangeRover

This worked!

Please Login or Register  to view this content.

And this works:

Please Login or Register  to view this content.

[RogerRangeRover]

Hi Kevin,

Thanks alot for these formulas! Much appreciated. What is the difference between a ";" and a "," in a COUNTIFS formula when you separate criterias and ranges? Sorry for the silly question! It seems like it is the key to use a comma instead of ; in this case?
Thanks again
Roger

[Kevin UK]

Hi Roger

I think it is a regional setting. In the UK we use "," .

Kevin