Home

What is the best way to find the sum of cells that meet a specific criteria?

%3CLINGO-SUB%20id%3D%22lingo-sub-874558%22%20slang%3D%22en-US%22%3EWhat%20is%20the%20best%20way%20to%20find%20the%20sum%20of%20cells%20that%20meet%20a%20specific%20criteria%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-874558%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20log%20that%20adds%20up%20delays%20for%20each%20specific%20crane.%20In%20the%20range%20of%20cranes%2C%20some%20of%20the%20numbers%20overlap%2C%20like%2C%20204%2C%20205%2C%204%2C%20and%205.%20Previously%2C%20I%20though%20I%20would%20be%20able%20to%20add%20the%20range%20by%20using%20the%20following%20function%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-c%22%3E%3CCODE%3E%3DSUMPRODUCT(--ISNUMBER(SEARCH(%22204%22%2CE8%3AE19))%2CC8%3AC19)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BE8%3AE19%20is%20the%20range%20of%20cranes%2C%20C8%3AC19%20is%20the%20amount%20of%20delays.%20Because%20our%20data%20is%20imputed%20with%20multiple%20cranes%20sharing%20a%20delay%3B%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E204%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E204%20205%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E205%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Ethis%20formula%20outputs%2015%20minutes%20for%20204%20and%2020%20minutes%20for%20205%2C%20which%20is%20correct.%20However%2C%20since%20we%20have%20numbers%20such%20as%204%20or%205%20in%20the%20mix%2C%20it%20will%20pull%20the%20data%20from%20204%20or%20205%20also%2C%20like%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E204%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E204%20205%3C%2FTD%3E%3CTD%3E%3CP%3E10%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E205%3C%2FTD%3E%3CTD%3E%3CP%3E10%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E%3CP%3E10%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EWhen%20adding%20the%20sum%20for%20crane%204%2C%20this%20formula%20outputs%20it%20being%2025%2C%20because%20its%20also%20pulling%20data%20from%20204%20as%20well.%20Is%20there%20any%20other%20way%20I%20can%20grab%20this%20data%20without%20it%20overlapping%3F%20I%20also%20tried%20using%20SUMIF%20but%20it%20does%20not%20recognize%20two%20cranes%20in%20the%20same%20cell.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-874558%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-875187%22%20slang%3D%22en-US%22%3ERe%3A%20What%20is%20the%20best%20way%20to%20find%20the%20sum%20of%20cells%20that%20meet%20a%20specific%20criteria%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-875187%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415013%22%20target%3D%22_blank%22%3E%40Ckalwick%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20something%20like%20this...%3C%2FP%3E%3CP%3E%3CU%3ESum%20for%20crane%204%3A%3C%2FU%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMPRODUCT(--ISNUMBER(SEARCH(%22%204%20%22%2C%22%20%22%26amp%3BE8%3AE19%26amp%3B%22%20%22))%2CC8%3AC19)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3ESum%20for%20crane%20204%3A%3C%2FU%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMPRODUCT(--ISNUMBER(SEARCH(%22%20204%20%22%2C%22%20%22%26amp%3BE8%3AE19%26amp%3B%22%20%22))%2CC8%3AC19)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20better%20to%20use%20a%20cell%20reference%20instead%20of%20using%20the%20hard%20coded%20values%20like%204%20and%20204%20in%20the%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ckalwick
Visitor

I am working on a log that adds up delays for each specific crane. In the range of cranes, some of the numbers overlap, like, 204, 205, 4, and 5. Previously, I though I would be able to add the range by using the following function;

 

 

 

 

 

=SUMPRODUCT(--ISNUMBER(SEARCH("204",E8:E19)),C8:C19)

 

 

 

 

 

 E8:E19 is the range of cranes, C8:C19 is the amount of delays. Because our data is imputed with multiple cranes sharing a delay; 

2045
204 20510
20510

this formula outputs 15 minutes for 204 and 20 minutes for 205, which is correct. However, since we have numbers such as 4 or 5 in the mix, it will pull the data from 204 or 205 also, like;

2045
204 205

10

205

10

4

10

When adding the sum for crane 4, this formula outputs it being 25, because its also pulling data from 204 as well. Is there any other way I can grab this data without it overlapping? I also tried using SUMIF but it does not recognize two cranes in the same cell. 

1 Reply

@Ckalwick 

You may try something like this...

Sum for crane 4:

=SUMPRODUCT(--ISNUMBER(SEARCH(" 4 "," "&E8:E19&" ")),C8:C19)

 

Sum for crane 204:

=SUMPRODUCT(--ISNUMBER(SEARCH(" 204 "," "&E8:E19&" ")),C8:C19)

 

It's better to use a cell reference instead of using the hard coded values like 4 and 204 in the formula.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies