COUNTIFS function with multiple criteria

Paul Newcome

@Sam H. Try replacing each

Attempt@row

with

@cell

Sam H.

I had tried that before with the same result:

Paul Newcome

@Sam H. Does that error exist in any cell within either of the two ranges?

Sam H.

I just reviewed the data and no it does not.

Paul Newcome

@Sam H. I'm sorry for missing it. Move one of the closing parenthesis from the very end to before the "greater than" symbol. The OR function needs to be closed out.

Sam H.

Oh yes that solved a piece of it. Now though it is checking every box as a duplicate entry. This example shows 3 different attempts so they should not be checked. I only want it to check if there are two 2nd Attempts, 1st Attempts, or 3rd Attempts for the same person. Thoughts?

Paul Newcome

@Sam H. In that case you would need to remove the OR function completely and replace it with just Attempt@row.

Sam H.

That's what I was missing. Thank you.

JLK

I'm having a similar issue. I want to count all the instances from another sheet where ColumnA is "X" and Column B is any of "1,2, or 3"

I'm using this formula:

=COUNTIFS({CRL ALL - Race}, [Primary Column]@row, {CRL ALL - Best_Status}, OR(@cell = "Hired", @cell = "Phone Screened", @cell = "Interviewed", @cell = "Offer Extended", or @cell = "Offer Rejected") )

I get #UNPARSABLE

Here are some things I've done to trouble shoot:

a: The formula works as =COUNTIF({CRL all applicants Range 1}, [Primary Column]@row)

so the problems seems to be in the second criteria. Can you not use the @cell when pulling from another sheet?

b: When I use this formula from the original sheet, it works, so there is something up with the second part of the formula for sure.

=COUNTIFS(Race:Race, "Black/African American", [Best_Status]:[Best_Status], OR(@cell = "Phone Screened", @cell = "Interviewed", @cell = "Offer Extended", @cell = "Offer Rejected", @cell = "Hired"))

What am I missing?

UPDATE: I couldn't seem to delete the comment, but wanted to let everyone know I did get it to work. I just retyped the formula again and somehow, it worked:

=COUNTIFS({CRL ALL - Race}, [Primary Column]@row, {CRL ALL - Best_Status}, OR(@cell = "Phone Screened", @cell = "Interviewed", @cell = "Offer Extended", @cell = "Offer Rejected", @cell = "Hired"))

Honestly have no idea what is different, but maybe my trouble shooting steps will help someone else, so I'll leave it.

Trouble shoot:

1. test each part of the formula independently
2. test on the origin sheet if using cross sheet formulas
3. start over and retype the formula from scratch in case you've made a typo somewhere

Genevieve P.

Hey @JKL

Thanks for posting your update and your troubleshooting steps!

Looks like in your first formula there was an or typed into your criteria at the end that was causing the error:

...d", or @cell = "Offer Rejected") 

Cheers,

Genevieve

Jenni Crutcher

Hello,

I'm trying to create a sheet summary formula where I am trying to sum up the total number of checkboxes in one column based on criteria in another column.

Based on various other discussions I've drafted up this formula that is not currently working: =COUNTIFS([Pre or Post Shipment Requirement]:[Pre or Post Shipment Requirement], @cell = "Pre-Shipment"), Submitted:Submitted, @cell = 1)

I want the summary to sum up any boxes that are checked in the "Submitted" column but only if the 'Pre or Post Shipment Requirement" cell for that row is filled in with "Pre-Shipment".

I've tried various iterations of this formula with no success. I think I'm close, but am struggling to make this work.

Thanks in advance for any assistance you can provide!

Jenni

Paul Newcome

@Jenni Crutcher Try getting rid of that closing parenthesis after "Pre-Shipment".

Jenni Crutcher

@Paul Newcome - well that was easy! :) THANK YOU SO MUCH!!!

Paul Newcome

@Jenni Crutcher Happy to help. 👍️

MPath

Hi @Genevieve P. , sorry for bumping this topic, I thought would be better to ask here, than to open a new topic?

I have done a twenty-some YTD counts (with a lot of leeching from discussion board here :) ) but they all had less creteria than what I need to do now.

I can't get my head round the following;

=COUNTIFS(HoofdCategorie:HoofdCategorie; "Documentatie"; [Categorie Documentatie]:[Categorie Documentatie]; OR(@cell = "Test-instructies"; @cell = "Assemblage-instructies"; Datum:Datum; >DATE(YEAR(TODAY()); 1; 1)))

I get a #Invalid Data type error.

I THINK it might have to do with the amount if creteria.

I did get the following to work earlier on:

=COUNTIFS(HoofdCategorie:HoofdCategorie; "Documentatie"; [Categorie Documentatie]:[Categorie Documentatie]; "Test-instructies"; Datum:Datum; >DATE(YEAR(TODAY()); 1; 1))

But I additionally need to have Assemblage-instructies, Test-instructies, Schema (elektrisch) and Anders, vermeld in omschrijving in there instaed of Order. Consolidating that count because they are within the same department.