Sum multiple values based on a certain text

hello All

I need a formula to sum a range of value based on certain texts, the list is dynamic and often changes
J11 – Formula to sum all LHL INS (highlighted in yellow)
J12 – sum all N/L INS (Highlighted in Green)
J13 – sum all RHL INS (Highlighted in Light Blue)

J14 – sum all LHL (Highlighted in dark Blue)
J15 – sum all N/L (Highlighted in Purple)
J15 – sum all RHL (Highlighted in Red)


Please see attached (colours are for examples only)

Paul

You can use this formula in J11:

=SUMIFS(D:D,C:C,I11&"*")

and then copy down. The asterisk is a wildcard character, meaning "anything else".

Hope this helps.

Pete

the formula can't distinguish between N/L INS & N/L

it needs to return separate values, It combines N/L INS & N/L when it comes to return N/L

For J15 You need this =SUMIFS(D:D,$C:$C,$I15&"*",$C:$C,"<>"&"*INS*")

Or……………….

In J11 copied down :

=SUMIFS(D:D,C:C,I11&"*",C:C,IF(RIGHT(I11)="S","<>","<>*INS*"))

Regards
Bosco

Or you could just subtract J12 from the formula in J15, and J13 at the end of the formula in J16.

Hope this helps.

Pete

thanks to all

both solutions work perfect.