Attempting a formula with multiple criteria
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
Best Answers
-
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".
-
@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).
Answers
-
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.
-
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")
-
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?
-
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:
-
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.
-
The first thing I notice is that your references are not set up correctly. Here is how your Countifs formula should be set up.
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.
-
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
-
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".
-
@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).
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!