COUNTIF with dollar ranges

Shannon E.

I'm looking for a COUNTIF formula for certain dollar levels listed below. My range is "FY24 Range 6"

greater than or equal to $1, but less than or equal to $500,000

greater than or equal to $500,001, but less than or equal to $1M

greater than or equal to $1M, but less than or equal to $3M

greater than or equal to $3M, but less than or equal to $5M

greater than or equal to $5M

Paul Newcome

You would use something like this:

=COUNTIFS({Range}, AND(@cell>= 1, @cell<= 500000))

Paul Newcome

You would use something like this:

=COUNTIFS({Range}, AND(@cell>= 1, @cell<= 500000))

Shannon E.

@Paul Newcome Thank you! That worked! Here is my final formulas for the ranges for anyone needing them:

$1-500,000 - =COUNTIFS({Submitted Proposals FY24 Range 6}, AND(@cell >= 1, @cell <= 500000))

$500,000-1M - =COUNTIFS({Submitted Proposals FY24 Range 6}, AND(@cell >= 500001, @cell <= 1000000))

1M-3M - =COUNTIFS({Submitted Proposals FY24 Range 6}, AND(@cell >= 1000001, @cell <= 3000000))

3,000,001 - 5M - =COUNTIFS({Submitted Proposals FY24 Range 6}, AND(@cell >= 3000001, @cell <= 5000000))

Above $5M - =COUNTIFS({Submitted Proposals FY24 Range 6}, AND(@cell >= 5000001))

Paul Newcome

Happy to help. 👍️

You actually don't need the AND in that last one since it only has one piece of criteria. Also keep in mind that (if you can have cents) you could miss out on some of those in between numbers such as $0.99 or $500,000.01.