How to formulate below?

RobertZapata

Hi team,

to get someone help me with my issue as i can't seem to get the formula working if i wanted to count the current status with date ranges? I have read and tried all exmaples but my formula seems to be wrong all the time.

As you could see i wanted to count these current status based on on the issuing date. i have added alot of dates to find the correct way to formulate but i have a dead end every time.

Thanks to help me out.

Rob

Paul Newcome

Try this:

=COUNTIF({Date of request}, IFERROR(MONTH(@cell), 0) = 4)

Paul Newcome

What is the formula you are currently using that has the error in the screenshot?

RobertZapata

Hi Paul,

=COUNTIFS({Current status}, [Column14]@row, {QM10 F01 Store consumable issue note Rev6 Range 1},>=DATE(2023,4,1){QM10 F01 Store consumable issue note Rev6 Range 1}, <=DATE(2023,4,30)

As wanted to get count of "issued" per month as this is what i wanted to happened.

Thanks

Rob

Paul Newcome

It looks like you are just missing a comma there between the first DATE function and the last range.

RobertZapata

Hi Paul,

It does and thanks but actually the value was registering zero which i find weird as there should be count based on data file.

I am totally lost right now to get the values right.

RobertZapata

Hi Paul again,

Please see my computation as the ones colored was done thru countif without date range and when i put countifs with date range i get a zero value. The issued and rest are from a dropdown column on the data sheet and i was trying to get a summary report to create a dashboard. Is there a way to number of issued, not issued, etc per month?

formula used for countif is: =COUNTIF({QM10 F01 Store consumable issue note Rev6 Status}, [Primary Column]@row)

formula used for countifs with date range is: =COUNTIFS({Current status}, [Column14]@row, {Issuing date}, <=DATE(2023, 4, 1), {Issuing date}, >=DATE(2023, 4, 30))

If you want to gain access i am all open for it as i have been trying to find a formula to get count of these dropdowns.

Thanks,

Rob

Paul Newcome

Try switching your arguments. You are currently only counting dates that are less than 1 April and greater than 30 April (which isn't possible). I believe you actually want greater than 1 April and less than 30 April.

RobertZapata

Hi Paul,

Thanks and i have gotten it.

Much appreciated!

Rob

RobertZapata

Hi Paul,

I would also need help to count the number of rows per month as i sused below formula.

formulaused: =COUNTIF({Date of request},MONTH({Date of request}4)

Thanks in advance as struggling hard to find the correct formula.

Rob

Paul Newcome

Try this:

=COUNTIF({Date of request}, IFERROR(MONTH(@cell), 0) = 4)

RobertZapata

Thanks Paul!

your the best!

Rob

Paul Newcome

Happy to help. 👍️