SOLVED

COUNTIFS more values for each criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2082893%22%20slang%3D%22es-ES%22%3ECOUNTIFS%20more%20values%20for%20each%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2082893%22%20slang%3D%22es-ES%22%3E%3CP%3EDear%20colleagues%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20got%20stuck%20at%20a%20point%20I'm%20in%20need%20of%20you%20expert%20advice.%3A)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wonder%20whether%20is%20it%20feasible%20to%20pick%20more%20than%20one%20value%20for%20each%20criteria.%20For%20instance%2C%20given%20the%20following%20expression%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECOUNTIFS('All%20OEs'!%20D%3AD%3B%22%20Greengrocer's%22%3B'%20All%20OEs'!%20J%3AJ%3B%22%20Orange%22%3B'%20All%20OEs'!%20X%3AX%3B%22%20Below%20Midpoint%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20add%20another%20value%20to%20be%20counted%20along%20%22Orange%22.%20Let's%20say%20I%20want%20the%20system%20to%20count%20oranges%20and%20apples%20at%20the%20same%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20happen%20to%20know%20how%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2082893%22%20slang%3D%22es-ES%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2083123%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20more%20values%20for%20each%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2083123%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F937707%22%20target%3D%22_blank%22%3E%40Asa94%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUM(COUNTIFS('All%20OEs'!D%3AD%3B%22Greengrocer's%22%3B'All%20OEs'!J%3AJ%3B%7B%22Apple%22%2C%22Orange%22%7D%3B'All%20OEs'!X%3AX%3B%22Below%20Midpoint%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2086973%22%20slang%3D%22es-ES%22%3ERe%3A%20COUNTIFS%20more%20values%20for%20each%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2086973%22%20slang%3D%22es-ES%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BI%20tried%20entering%20the%20following%20expression%3A%20.SUM(COUNTIFS('All%20OEs'!%20D%3AD%3B%22%20Greengrocer's%22%3B'%20All%20OEs'!%20G%3AG%3B%20%22Orange%22%2C%20%22Apple%22%3B'%20All%20OEs'!%20X%3AX%3B%22%20Below%20Midpoint%22))%20but%20it%20triggered%20an%20error%20message.%20Is%20there%20any%20other%20way%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Dear colleagues,

 

I got stuck at a point I'm in need of you expert advice.

 

I wonder whether is it feasible to pick more than one value for each criteria. For instance, given the following expression:

 

=COUNTIFS('All OEs'!D:D;"Greengrocer's";'All OEs'!J:J;"Orange";'All OEs'!X:X;"Below Midpoint")

 

I would like to add another value to be counted along "Orange". Let's say I want the system to count oranges and apples at the same time.

 

Do you happen to know how? 

 

Thanks a lot!

 

 

5 Replies

@Asa94 

You can use

 

=SUM(COUNTIFS('All OEs'!D:D;"Greengrocer's";'All OEs'!J:J;{"Apple","Orange"};'All OEs'!X:X;"Below Midpoint"))

@Hans Vogelaar I tried entering the following expression: =SUM(COUNTIFS('All OEs'!D:D;"Greengrocer's";'All OEs'!G:G;{"Orange","Apple"};'All OEs'!X:X;"Below Midpoint")) but it triggered an error message. Is there any other way?

 

Thanks a lot!

Best Response confirmed by Asa94 (New Contributor)
Solution

@Asa94 

 

Does this work?

 

=SUM(COUNTIFS('All OEs'!D:D;"Greengrocer's";'All OEs'!G:G;{"Orange"\"Apple"};'All OEs'!X:X;"Below Midpoint"))

 

(With SUM and COUNTIFS translated to SUMA and CONTAR.SI.CONJUNTO if necessary)

@Hans Vogelaar Works perfectly now!

BTW, may I use the same structure multiple times? Hence I'd be able to count Oranges and Apples but also "Below Midpoint" and "Around Midpoint"

@Asa94 

Yes, you can use this multiple times.