COUNTIFS function with multiple criteria

Paul Newcome

@Jessica Toledo Are you able to provide a screenshot of the formula as is in the sheet similar to the screenshot below?

P Challen

=COUNTIFS({REVISED SC Deal Info Data Only Range 3}, HAS(@cell, "September 2022", {REVISED SC Deal Info Data Only - Dashboard Range 2}, OR(@cell, "1 Newly Assigned", @cell = "2 RFI-RFP", @cell = "3 Qualification", @cell = "4 Analysis/Prep", @cell = "5 Discovery", @cell = "6 Solutioning", @cell = "7 Contracting - SOW ")))

I am using the above to get a count of how many deals are marked for the month of Sept 2022 and are in the 7 statuses listed but keep on getting 0 when I should have values. Any help would truly appreciated. Thank you

Paul Newcome

@P Challen Are you able to provide a screenshot of the source data? Right off I see some syntax issues, but I want to make sure that any suggestions fit with what you are actually working with.

P Challen

@Paul Newcome First screenshot is the formula used. Trying to create the formula to count if the project date falls in a certain month (2nd screenshot) and is also one of the 7 stages I have listed in the 3rd screenshot. Any help to what I am doing wrong would be truly appreciated. Thank you

P Challen

@Paul Newcome End game is to have the dates show in date order on my dashboard and not alphabetical so I created a ref sheet with the dates in the proper order. Thank you

Paul Newcome

@P Challen Try this:

=COUNTIFS({REVISED SC Deal Info Data Only Range 3}, @cell = "September 2022", {REVISED SC Deal Info Data Only - Dashboard Range 2}, OR(@cell = "1 Newly Assigned", @cell = "2 RFI-RFP", @cell = "3 Qualification", @cell = "4 Analysis/Prep", @cell = "5 Discovery", @cell = "6 Solutioning", @cell = "7 Contracting - SOW"))

P Challen

@Paul Newcome You are the man! Thank you!!

Paul Newcome

@P Challen Happy to help. 👍️

Robert O.

Hello,

I am attempting to use CountIFs via reference sheet. I have an Issue Type column with multi-select dropdown properties that shows which Firms had issues. Some have multiple, different issues.

I am trying to count the number of times each Issue Type occurs with a Firm. The data lives in another sheet.

I am using this formula (=COUNTIFS({Task Log Range 1}, "Firm", {Task Log Range 2}, "Files Missing") but the value returned is always 0 even though the value should be at least 3.

I've tried these formulas as well with no luck:

=COUNTIFS({Task Log Range 1}, "Firm", {Task Log Range 2}, HAS({Task Log Range 2}, "Files Missing")

=COUNTIFS({Task Log Range 1}, "Firm", {Task Log Range 2}, OR(CONTAINS("Files Missing", {Task Log Range 2})))

Please advise

Paul Newcome

@Robert O. You would use an @cell reference inside of the HAS function to tell the formula to evaluate the previously established range on a cell by cell basis.

=COUNTIFS({Task Log Range 1}, "Firm", {Task Log Range 2}, HAS(@cell, "Files Missing")

Jest1020

Hi all,

I have been having trouble doing multiple COUNTIFS in one formula. The one I am trying to use is below but keeps giving me Invalid Operations error.

=COUNTIFS({Tracker Template 2.0 Range 1}, <>"Open Position", {Tracker Template 2.0 Range 3} <> "Leader Name X", {Tracker Template 2.0 Range 5}, "Draft")

Paul Newcome

@Jest1020 It looks like you just forgot a comma after your second range is all.

Jest1020

Thank you, I added the common but results come back with zero unfortunately :(

Paul Newcome

@Jest1020 Try using "@cell" references:

=COUNTIFS({Tracker Template 2.0 Range 1}, @cell <> "Open Position", {Tracker Template 2.0 Range 3}, @cell <> "Leader Name X", {Tracker Template 2.0 Range 5}, @cell = "Draft")

Samantha S.

I'm trying to use this formula to check for duplicates by checking a box if it seems two of the same number attempt recorded for an individual, but I keep getting an INCORRECT ARGUMENT error. Any ideas where I am going wrong?