Forum Discussion
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.
1 Reply
- Subodh_Tiwari_sktneerSilver Contributor
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.