Attempting a formula with multiple criteria

Andrew Darley

Hi,

I've 2 columns named "Audit Completed by" which contains a result from a contact list and "Audit Result" which gives dropdown result of pass or fail.

Trying to give a sum of when both have a specific username and pass as below but keep getting #UNPARSEABLE result.

=COUNTIFS([Audit Completed by]:[Audit Completed by], "Joe Bloggs", [Audit Result]:[Audit Result], "Pass")

Any ideas?

Thanks,

Andrew

Paul Newcome

The formula with the syntax corrected:

=COUNTIFS({Installation Fitters Audit Range 1}, "Fail", {Installation Fitters Audit Range 1}, "joebloggs@joebloggs.com")

Will still not work because you are using the same range for two different criteria. Your formula is basically saying to count if a cell equals "Fail" and the same cell equals "joebloggs@joebloggs.com".

Since "joebloggs@joebloggs.com" does not equal "Fail", even if cross sheet references and syntax are correct you will always get a result of 0.

You are going to want to create one range for "Fail" and a different range for "joebloggs@joebloggs.com".

Mike Wilday

@Paul Newcome Good catch. I swore that was different. You'll have to set a separate cross referenced range from the same sheet to get the email. Just create two different cross sheet references from the same sheet (one column each).

Mike Wilday

Try using the contact's email address instead of their name. Referencing contact columns can be tricky. I tend to try both name and email till I get the result I want.

Andrew Darley

Thanks Mike, sadly no it still kicks back.

Have revised the look up as below but still no luck:

=COUNTIFS({Installation Fitters Audit Range 1} ([Audit Result]1:[Audit Result]3, "Fail", [Audit Completed by]1:[Audit Completed by]3, "joebloggs@jobloggs.co.uk")

Paul Newcome

Your original formula should be working. Can you copy/paste the exact formula from your sheet?

Is the data you are trying to count on the same sheet as the formula or a different one?

Mike Wilday

It looks like your missing a comma or added some extra information in this one...

=COUNTIFS({Installation Fitters Audit Range 1} ([Audit Result]1:[Audit Result]3, "Fail", [Audit Completed by]1:[Audit Completed by]3, "joebloggs@jobloggs.co.uk")

The Installation Fitteres Audit Range one is pointing to an external source, but the the others are pointing to a source on the internal sheet. If you're looking for all this data in an external sheet just make sure that each range is its own cross sheet reference. Instead of [Audit Result]1:[Audit Result]3, you'd have a cross sheet reference for that range in the opposite sheet. And then a new cross sheet reference for the [Audit Completed by]1:[Audit Completed by]3 range as well. Does that make sense?

For more on Cross Sheet References see:

https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

Andrew Darley

Paul, Mike,

The data for Audit Result (fail) and Audit completed by (joebloggs@joebloggs.com) is held on the same external sheet referenced as Installation fitters Audit Range 1.

Current exact formula:

=COUNTIFS({Installation Fitters Audit Range 1} ([Audit Result]1:[Audit Result]3, "Fail", {Installation Fitters Audit Range 1} [Audit Completed by]1:[Audit Completed by]3, "joebloggs@joebloggs.com")

Underneath is marked as the edit reference.

Mike Wilday

The first thing I notice is that your references are not set up correctly. Here is how your Countifs formula should be set up.

https://help.smartsheet.com/function/countifs

COUNTIFS(range1, criterion1, [range2, ​criterion2​...])

From your formula, it looks like your trying to reference the cross sheet formula AND a range from that formula. That won't work. Your Cross sheet references ARE your ranges. Remove the Bold and Italic parts of your formula and see if that works.

=COUNTIFS({Installation Fitters Audit Range 1} ([Audit Result]1:[Audit Result]3, "Fail", {Installation Fitters Audit Range 1} [Audit Completed by]1:[Audit Completed by]3, "joebloggs@joebloggs.com")

Let me know if that works.

Andrew Darley

Mike,

Have copied and pasted your revised formula above without the bold and italic items, but still having the same error. Have tried to change the "joebloggs@joebloggs.com" to "Joe Bloggs" to see if this would change it.

I can get the formula to work on the sourcesheet without issue but not on the 2nd

Paul Newcome

The formula with the syntax corrected:

=COUNTIFS({Installation Fitters Audit Range 1}, "Fail", {Installation Fitters Audit Range 1}, "joebloggs@joebloggs.com")

Will still not work because you are using the same range for two different criteria. Your formula is basically saying to count if a cell equals "Fail" and the same cell equals "joebloggs@joebloggs.com".

Since "joebloggs@joebloggs.com" does not equal "Fail", even if cross sheet references and syntax are correct you will always get a result of 0.

You are going to want to create one range for "Fail" and a different range for "joebloggs@joebloggs.com".

Mike Wilday

@Paul Newcome Good catch. I swore that was different. You'll have to set a separate cross referenced range from the same sheet to get the email. Just create two different cross sheet references from the same sheet (one column each).