#CALCULATING Error - Incorrectly Stating Over 100000 Cells Referenced

johnjrmot

Good morning,

I am receiving a #CALCULATING error for the below formula:

=COUNTIFS({Mobile Unit Data Form Range 15}, =1, {Mobile Unit Data Form Range 3}, "EMPLOYEE", {Mobile Unit Data Form Range 1}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Range 15 is a checkbox, Range 3 is the Employee's name, and I only want to see results from current month, using Range 1 which is the date of entry. There are only 3290 rows with data, so I am not sure how over 100000 cells are being referenced, even if all three ranges referenced count towards the total.

Can anyone please let me know why this would be happening?

johnjrmot

As an update - does this look at all formulas on the sheet? Because I am using a "calculation sheet" with many other formulas, and am only now encountering this issue.

Frank B.

The #CALCULATING error you're seeing in your formula likely means that the formula is taking too long to calculate and has exceeded the maximum allowed calculation time. This can happen if you're referencing a large number of cells, or if your formula is complex and requires a lot of processing.

One thing to note is that the COUNTIFS function allows you to specify multiple criteria, but it does so by combining them with an AND operator. In your formula, you're trying to use the AND function within the COUNTIFS function, which isn't allowed. Instead, you can separate the date criteria and use two separate COUNTIFS functions.

Here's a revised version of your formula:

=COUNTIFS({Mobile Unit Data Form Range 15}, 1, {Mobile Unit Data Form Range 3}, "EMPLOYEE", {Mobile Unit Data Form Range 1}, ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1), {Mobile Unit Data Form Range 1}, "<="&DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, {Mobile Unit Data Form Range 1}, "<>NULL")

This formula uses the DATE function to get the first and last day of the current month and compares them to the date in the specified range. The "<>"&NULL condition excludes any blank cells in the date column.

If you're still seeing the #CALCULATING error with this revised formula, you can try breaking it down into smaller parts to identify which part of the formula is causing the issue. You can also try optimizing your sheet by reducing the number of cells being referenced, or by splitting up the data into smaller sheets.

HTH!