Help on formula: count if with multiple criteria

Megan Doyle

@Paul Newcome Good ole turn it off, turn it on worked!

Happy dancing at my desk! THANK YOU!

Paul Newcome

@Megan Doyle Happy to help. 👍️

Josh Canady

I've tried to recreate some of the formulas from above but I haven't any luck. I need a formula for counting how many rows have Josh Canady in the Assigned To column and Install in the Order Type column.

Genevieve P.

Hi @Josh Canady

Is the assigned-to multi select or single select?

If it's single select, try this:

=COUNTIFS([Assigned To]:[Assigned To], "Josh Canady", [Order Type]:[Order Type], "Install")

If this hasn't worked for you, it would be helpful to see the formula you tried open in Smartsheet!

Thanks,

Genevieve

Josh Canady

That worked! Thank you so much!

L.R.

This thread is so informative! Thank you to everyone who has contributed! I was able to use a combo of the formulas provided but am stuck. I am trying to count everything with a status of "Awarded" on two different sheets that meet the criteria of "RFP". When I use the formula:

=COUNT(COUNTIFS({PENDING type}, "RFP", @cell = "Awarded"), COUNTIFS({FINAL type}, "RFP", @cell = "Awarded"))

I get a total of 2, which I know is wrong because my filters are tallying at 17. Any idea what I'm doing wrong?

Ideally, I would be able to get this to also count from a date range starting at the fiscal year of Oct. 1, 2023 (which I have yet to attempt). Would anyone know how I can achieve this as well?

Genevieve P.

Hi @L.R.

Instead of wrapping your two COUNTIFS statements in a COUNT (which will count 2 because you have 2 Countifs), you'll want to use SUM to add together the outputs, or simply use + between the two statements.

You're also missing a second reference to the column where it has "Awarded" listed.

Try:

=COUNTIFS({PENDING type}, "RFP", {Awarded Column Reference Sheet 1}, @cell = "Awarded") + COUNTIFS({FINAL type}, "RFP", {Awarded Column Reference Sheet 2}, @cell = "Awarded")

L.R.

Thanks @Genevieve P. for the quick reply. When I try the formula I get the message #INVALID OPERATION.

I've also tried the following unsuccessfully:

=SUM(COUNTIF({PENDING type}, "RFP", @cell = "Awarded"), COUNTIF({FINAL type}, "RFP", @cell = "Awarded") #INCORRECT ARGUMENT

=SUM(COUNTIFS({PENDING type}, "RFP", @cell = "Awarded"), COUNTIFS({FINAL type}, "RFP", @cell = "Awarded")) #INVALID OPERATION

Genevieve P.

Hi @L.R.

Two details here:

• Since you have two criteria, make sure your COUNTIF has an S on the end: COUNTIFS
• For each criteria you're looking for, you need to add the column or range before it, like so:

=COUNTIFS({Column 1}, "Criteria 1", {Column 2}, "Criteria 2")

This means you need a new range created for each sheet that's looking at your column with the "Awarded" data, does that make sense?

L.R.

I see what I did wrong! I forgot to map it to the sheet for the second criteria. Thank you so much @Genevieve P. ! I was also able to add the additional criteria of the specific date range.

Final formula is:

=COUNTIFS({PENDING type}, "RFP", {PENDING status}, @cell = "Awarded", {PENDING FY}, @cell >= DATE(2023, 10, 1), {PENDING FY}, @cell <= DATE(2024, 9, 30)) + COUNTIFS({FINAL type}, "RFP", {FINAL status}, @cell = "Awarded", {FINAL FY}, @cell >= DATE(2023, 10, 1), {FINAL FY}, @cell <= DATE(2024, 9, 30))

Genevieve P.

Awesome! I'm glad to see this worked for you 🙂

estradamee

Good afternoon,

I have a formula that intends to check a box if a duplicate entry of a number occurs, so long as a status column does/doesn't meet certain criteria. I am having trouble getting the count to "remove" those matching numbers where statuses should leave that row out of the count. The formula works to identify duplicates but still marks those with a "RECEIVED" status, for instance, as a duplicate.

=IF(COUNTIFS([Part #]:[Part #], [Part #]@row, [Procurement Status]:[Procurement Status], OR(@cell <> "RECEIVED", @cell <> "LOCKED", ISBLANK(@cell))) > 1, 1, 0))

My goal is to only have duplicates flagged if those other criteria are/aren't met, but at the moment it is showing duplicate entries regardless of status. Any help is most welcome, thanks!

Paul Newcome

@estradamee Try swapping your OR for an AND.

estradamee

@Paul Newcome

Thank you for the response. That solution didn't work, unfortunately.

I found a work around using another helper column to add a character to the part number if those status conditions are met and then use countifs for that column of part numbers.

Still odd that the Countifs isn't working how I would logically expect... Oh well.

Thanks again!

Paul Newcome

@estradamee I missed the ISBLANK argument.

=IF(COUNTIFS([Part #]:[Part #], [Part #]@row, [Procurement Status]:[Procurement Status], OR(AND(@cell <> "RECEIVED", @cell <> "LOCKED"), ISBLANK(@cell))) > 1, 1, 0))

Use the AND around the Received and Locked arguments but use the OR around the AND and ISBLANK.