I need help with a sumif and countif formula with multiple criteria, one criteria is falling between

Alyson O'Donnell

Hello I am trying to pull 2 different cross sheet reference formulas.

1.) SUMIF({Auto QA Checklist Range 2}, CONTAINS([Agent Name]1, @cell), {Auto QA Checklist Range 6}) _ I need this result to account for an AND statement, that pulls info between 2 date fields. I am creating a "Filter Sheet" where I can pull info based on "agent name" and "date range".

2.) COUNTIF({Auto QA Checklist Range 2}, CONTAINS([Agent Name]1, @cell)) - Similar to above, right now this is counting data that matches the "Agent Name" field I update. I need it to pull this number, AND further meet criteria between 2 date fields.

Paul Newcome

You are going to need to use a SUMIFS and a COUNTIFS to be able to incorporate additional range/criteria sets.

Genevieve P.

Hi @Alyson O'Donnell

It looks like you said Paul's response didn't resolve your issue, so I'll expand further on his suggestion. Currently you're using a singular SUMIF or COUNTIF formula, but when you need to look for multiple criteria in different columns you'll want to use a SUMIFS or COUNTIFS, plural, with the S on the end.

Here are the 2 related Help Center articles that go through the structure of each function: SUMIFS function / COUNTIFS function

***SUMIFS:***

SUMIFS is great because to build it all you need to do is list the range you want to SUM, then the range with criteria, the criteria, then the next range with criteria, and the next criteria. Since you have your dates built into the sheet already, it will be really easy to reference.

Now, I presume that Range 6 is what you want to SUM. You'll also need to add in a new cross-sheet reference that looks at the DATE column in the other sheet, too. I've called this {Date Column} in my example below.

Try This:

=SUMIFS({Auto QA Checklist Range 6}, {Auto QA Checklist Range 2}, CONTAINS([Agent Name]@row, @cell), {Date Column}, >=[Date Start]@row, {Date Column}, <=[Date End]@row)

Can I also ask why you have CONTAINS? If you know that the Agent Name will be exactly letter-for-letter the same as what's in your Agent Name column, you can list it as the criteria without the additional contains function:

Simplified formula:

=SUMIFS({Auto QA Checklist Range 6}, {Auto QA Checklist Range 2}, [Agent Name]@row, {Date Column}, >=[Date Start]@row, {Date Column}, <=[Date End]@row)

***COUNTIFS:***

Now the COUNTIFS formula will be even easier, as we don't need to list a range to SUM at all. It just goes Range, Criteria, Range, Criteria, etc.

Try This:

=COUNTIFS({Auto QA Checklist Range 2}, [Agent Name]@row, {Date Column}, >=[Date Start]@row, {Date Column}, <=[Date End]@row)

Here are some other Help Center articles I used to help build these out:

• Cross-Sheet References
• @row function
• Using < and >

Let me know if you have any questions about the above!

Cheers,

Genevieve

Paul Newcome

@Genevieve P Thanks. I was running short on time when I posted and forgot to come back with more detail.

Genevieve P.

@Paul Newcome haha no worries! You are absolutely correct in your answer 😊

Just thought I'd write a novel...