Incorrect argument set error message on countif formula

Drea Mora

I'm working on a project that distributes free car seats and we collect data from family members for up to 4 children per family depending on their car seat needs. One entry may include 1, 2, 3, and/or 4 children and we ask for specific demographic data per child. Again, all collected in entry so across multiple columns for one row.

In this scenario I am trying to calculate how many children were identified as having a certain racial identity within a certain time frame. It works when I write the formula without a time frame like this:

=COUNTIFS([Child Racial Identity 1]:[Child Racial Identity 4], "White")

But once I add a date range it says incorrect argument set and I don't understand why the addition of a date range would create this error message.

=COUNTIFS([Child Racial Identity 1]:[Child Racial Identity 4], "White", [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30)))

What am I doing wrong?

Jason Tarpinian

It should work if you expand your AND into 2 criteria instead of one:

=COUNTIFS([Child Racial Identity 1]:[Child Racial Identity 4], "White", [Date Distributed]:[Date Distributed], >=DATE(2023, 10, 1), [Date Distributed]:[Date Distributed], <=DATE(2024, 9, 30))

I also just realized your range is four columns for racial identity. So your formula does work, but I thought it was just one range. For a range of columns, you'd have to sum up each of the racial identities separately, like this.

=COUNTIFS([Child Racial Identity 1]:[Child Racial Identity 1], "White", [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30))) + COUNTIFS([Child Racial Identity 2]:[Child Racial Identity 2], "White", [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30))) + COUNTIFS([Child Racial Identity 3]:[Child Racial Identity 3], "White", [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30))) + COUNTIFS([Child Racial Identity 4]:[Child Racial Identity 4], "White", [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30)))

Jason Tarpinian

It should work if you expand your AND into 2 criteria instead of one:

=COUNTIFS([Child Racial Identity 1]:[Child Racial Identity 4], "White", [Date Distributed]:[Date Distributed], >=DATE(2023, 10, 1), [Date Distributed]:[Date Distributed], <=DATE(2024, 9, 30))

I also just realized your range is four columns for racial identity. So your formula does work, but I thought it was just one range. For a range of columns, you'd have to sum up each of the racial identities separately, like this.

=COUNTIFS([Child Racial Identity 1]:[Child Racial Identity 1], "White", [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30))) + COUNTIFS([Child Racial Identity 2]:[Child Racial Identity 2], "White", [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30))) + COUNTIFS([Child Racial Identity 3]:[Child Racial Identity 3], "White", [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30))) + COUNTIFS([Child Racial Identity 4]:[Child Racial Identity 4], "White", [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30)))