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]

Try:

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

 

[smuller74]

I tried and it seems to work except for "januari"... I checked it a view times but I do not understand why the calculation is not right for this month. Could it have something to do with an condition=0? (instead of "55" in the first condition I have "0")

 

[Andrew Poulsom]

Do you maybe have some blank cells in F2:F5000 for January?

 

[smuller74]

No, no blank cells... it's also more counted then records are on sheet2... (maybe it counts all conditions containing a zero like "10, 30..." ???)

 

[Andrew Poulsom]

Please post the exact formula you are using.

 

[smuller74]

=AANTAL(ALS((debiteurnummer!F2:F5000=0)*(debiteurnummer!Q2:Q5000=MAAND(("1"&F22)+0)*(debiteurnummer!A2:A5000=H4));(debiteurnummer!A2:A5000)))

 

[Andrew Poulsom]

For me (Excel 2003):

debiteurnummer!F2:F5000=0

returns TRUE for blanks and zeroes, but not text entries or entries containing a zero.

 

[smuller74]

Well, the data range is actually F2:F2278 but I picked range F2:F5000 to make sure all data will be counted as the data range is growing every day.

 

[Andrew Poulsom]

Without seeing your data I don't know why you are getting the wrong result, sorry.

 

[smuller74]

The formula is working correct but we have to exclude blanks.