COUNTIF for a date range - what am I doing wrong?

Nat

Hi all,

Is there something wrong with this formula? I just want to count how many rows fall in each financial year, and I can't tell what I've done wrong.

=COUNTIF ([Date of Request]:[Date of Request], (@cell >=DATE (2022, 4, 1), @cell <=DATE (2023, 3, 31)))

On a side note, didn't I read something about formula AI? I can't locate that anywhere!

JamesB

@Nat

Try the formula below. This will test both conditions against the column for validation.

=COUNTIF ([Date of Request]:[Date of Request], AND(@cell >=DATE (2022, 4, 1), @cell <=DATE (2023, 3, 31)))

Nat

Thanks @JamesB! I did have it that way before as well, and just copy-pasted - still #unparseable.

I'm double checking the column title but it's right. "Date of Request"

JamesB

@Nat Apologies, I did not notice the spaces in your formula build. It is not see the commands properly. Try this...

=COUNTIF([Date Of Request]:[Date Of Request], AND(@cell >= DATE(2022, 4, 1), @cell <= DATE(2023, 3, 31)))

Nat

@JamesB THANK YOU!!!

That works! Was it just the space before and after "<=" and ">+" ? I always thought space or no space, it didn't matter!

JamesB

@Nat It was actually the spaces between your formula types and the parentheses.

your post had:

=COUNTIF (

but a formula needs to be connected:

=COUNTIF(

Nat

That makes sense, thanks for clarifying!

Nat

Oh no! I forgot what I actually need is the sum of [Total Reports] for rows where [Date of Request] >=DATE (2022, 4, 1), @cell <=DATE (2023, 3, 31)))

My best guess is entirely wrong

=SUM([Total Reports]:[Total Reports], IF([Date of Request]:[Date of Request], AND(@cell >= DATE(2023, 4, 1), @cell <= DATE(2024, 3, 31))))

Nat

Adam Murphy

Try SUMIF rather than sum, it should work just fine.

JamesB

@Nat

You can use a sumif for this, it builds slightly different than a countif, try this formula.

SUMIF(Range you want to reference, what to look for in that range, where to sum the data from)

=SUMIF([Date of Request]:[Date of Request], AND(@cell >= DATE(2023, 4, 1), @cell <= DATE(2024, 3, 31)),[Total Reports]:[Total Reports])

Nat

Thanks @Adam Murphy and @JamesB

I almost had it by myself, minus one set of brackets - this one works now, thanks so much.