count records depending on selected month

[smuller74]

Hello all,

I'm getting confused looking for the correct formula and I hope somebody can help...

I'm using this array formula currently to count records:

=COUNT(IF((sheet2!F2:F5000=55)*(D22="january")*(sheet2!Q2:Q5000=1);(sheet2!A2:A5000)))

(D22="january") - In box "D22" I placed a drop-down to select a month.

This formula is working for "january" (= sheet2!Q2:Q5000=1) but how do I adjust the formula to make sure the calculations are correct per month if I change the month in "D22"?

Thanks for any help or any idea.

Best regards

 

[Andrew Poulsom]

Maybe try:

=COUNT(IF((sheet2!F2:F5000=55)*(sheet2!Q2:Q5000=MONTH(("1"&D22)+0));(sheet2!A2:A5000)))

 

[smuller74]

It seems to work but not if I select "january" (= sheet2!Q2:Q5000=1) and/or "february" (= sheet2!Q2:Q5000=2)...

 

[Andrew Poulsom]

What happens then? If you enter:

MONTH(("1"&D22)+0)

in a cell what does it return for january?

 

[smuller74]

it returns "0"... the same for february... (but there are records in these month)

 

[Andrew Poulsom]

What country are you in?

 

[smuller74]

The Netherlands, I changed MONTH into MAAND or is there more I need to change?

 

[Andrew Poulsom]

You probably also need to translate the month names into Dutch.

 

[smuller74]

Yes, that was it! Thank you very much Andrew!

 

[smuller74]

I have an additional question. I wanted to add another condition like
"(sheet2!A2:A5000);H4;". In H4 you can enter a customer number to see only results of this customer. Can we integrate it anyhow to the excisting formula?

=COUNT(IF((sheet2!F2:F5000=55)*(sheet2!Q2:Q5000=MONTH(("1"&D22)+0));(sheet2!A2:A5000)))

Thanks again.