Sumifs unique text

%3CLINGO-SUB%20id%3D%22lingo-sub-2036801%22%20slang%3D%22en-US%22%3ESumifs%20unique%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2036801%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20used%20a%20sumifs%20formula%20to%20count%20the%20number%20of%20colours%20for%20a%20given%20date%20-%20formula%20and%20results%20in%20blue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20now%20want%20to%20count%20the%20unique%20number%20of%20candidates%20per%20colour%20for%20a%20given%20date%20-%20results%20in%20yellow%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20much%20appreciated%20-%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERichard%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2036801%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2036868%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20unique%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2036868%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F920041%22%20target%3D%22_blank%22%3E%40richardcresswell%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDepends%20on%20your%20version%20of%20Excel%2C%20as%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTA(UNIQUE(FILTER(%24C%242%3A%24C%2410%2C(%24B%242%3A%24B%2410%3DG3)*(%24D%242%3A%24D%2410%3DH3))))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Regular Visitor

Hi,

 

I have used a sumifs formula to count the number of colours for a given date - formula and results in blue.

 

I now want to count the unique number of candidates per colour for a given date - results in yellow?

 

Any help would be much appreciated - attached.

 

Thanks,

 

Richard

1 Reply

@richardcresswell 

Depends on your version of Excel, as variant

=COUNTA(UNIQUE(FILTER($C$2:$C$10,($B$2:$B$10=G3)*($D$2:$D$10=H3))))