CODEWITHSUNDEEP
excel
Stewie Griffin
Stewie Griffin

Reputation: 14939

Sum rows, where other column contains certain phrase

I have an Excel-file with something like this:

19-FM05     Heater      2.0 4.0
19-PB09 (M) Motor HPU   4.0 6.0
19-PB10A(M) Fan Motor   6.0 2.0
19-PB11A(M) Motor WCU   3.0 7.0
            Motor      13.0 15.0
            Other       2.0 4.0
            Sum        15.0 19.0

I want to be able to differentiate the columns that includes (M) in column A, or the word Motor or motor in column B, and then sum all motor-rows, and and all other rows.

There might be spaces after (M), and all rows containing (M) also contain the word Motor in the next column.

I'm not used to using the more advanced stuff (e.g. VBA) in Excel, and can't find a way to do this with vlookup, find or something similar. Any suggestions?

Upvotes: 1

Views: 537

Answers (4)

pnuts
pnuts

Reputation: 59440

I suggest a helper column (say A) with =IF(IFERROR(FIND("M)",B2),0)+IFERROR(SEARCH("motor",C2),0)>0,"TRUE","") copied down and =SUMIF($A2:$A5,"TRUE",D2:D5) to the right of Motor and copied across, with Other calculated as the difference between Motor and Sum and the latter calculated with AutoSum.

Upvotes: 0

bushell
bushell

Reputation: 550

=IFERROR(IF(FIND("(M)",A1)>0,"Yes","No"),"No")

If you stick this in column D it will return yes if A1 contains "(M)" and no if it doesn't. That will help you filter, and you can play around with that. Then you can use the SUMIF function to sum rows where column D contains Yes.

There is probably a more elegant way of doing it, but this keeps it simple, and is readable.

Upvotes: 0

KekuSemau
KekuSemau

Reputation: 6856

This should work with SUMPRODUCT. You can use it to calculate row-wise products and sum these up. And in this, you can use conditions that are evaluated to 1 or 0. The Motors sum (13.0 in C5) would be: 

=SUMPRODUCT(NOT(ISERROR(FIND("(M)",A1:A4)))*(C1:C4))

Upvotes: 1

asantaballa
asantaballa

Reputation: 4038

The approach I use is to make a column from the number column that contains the number if the condition is met or 0 otherwise. So in your case make a column using the formula

=IF(ISNUMBER(SEARCH("(M)",A1)),C1,0)

In row one and then copying it down the column. Then when you sum that new colume you will get a sum of only the row containing “(M”)

Upvotes: 0

Related Questions