• 408K Members
• 8,837 Online
• 464K Conversations

Visitor

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

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;

 204 5 204 205 10 205 10

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;

 204 5 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.

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

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