SOLVED

Help with counting formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1280469%22%20slang%3D%22en-US%22%3EHelp%20with%20counting%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1280469%22%20slang%3D%22en-US%22%3EHi%20there.%20I%20need%20some%20help%20with%20a%20formula%20to%20count%20a%20projects%20based%20on%20criteria%20in%20two%20different%20columns.%3CBR%20%2F%3E%3CBR%20%2F%3ESee%20attached%20for%20what%20I'm%20looking%20for.%20I%20tried%20a%20previously%20suggested%20formula%2C%20though%20it%20counts%20DKNS%20projects%20as%20a%201%20for%20some%20reason.%3CBR%20%2F%3E%3CBR%20%2F%3ESuggestions%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks!!%3CBR%20%2F%3E%3CBR%20%2F%3EHelp%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1280469%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-1280611%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20counting%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1280611%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F492141%22%20target%3D%22_blank%22%3E%40AL789%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20lies%20in%20the%20fact%20that%20all%20your%20entries%20in%20column%20C%20are%20texts.%20%22DKNS%22%20also%20qualifies%20as%20it%20is%20seen%20as%20greater%20than%20one%20(i.e.%20%26gt%3B1).%20Make%20sure%20that%20you%20enter%20revenues%20as%20numbers%20and%20then%20test%20for%20numbers%20in%20column%20C.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%7B%3DSUM(IF((Table1%5BCentral%20Region%3A%20Expand%5D%3D%22Expand%22)%2BISNUMBER((Table1%5BCentral%20Region%3A%0AExpand%3A%20Sales%20revenue%20%24%5D))%2C1%2C0))%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BAttached%20a%20revised%20schedule%20with%20the%20working%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
Hi there. I need some help with a formula to count a projects based on criteria in two different columns.

See attached for what I'm looking for. I tried a previously suggested formula, though it counts DKNS projects as a 1 for some reason.

Suggestions?

Thanks!!

Help?
1 Reply
best response confirmed by AL789 (Occasional Contributor)
Solution

@AL789 

The problem lies in the fact that all your entries in column C are texts. "DKNS" also qualifies as it is seen as greater than one (i.e. >1). Make sure that you enter revenues as numbers and then test for numbers in column C.

{=SUM(IF((Table1[Central Region: Expand]="Expand")+ISNUMBER((Table1[Central Region:
Expand: Sales revenue $])),1,0))}

 Attached a revised schedule with the working formula.