How to SUMIF from cells that contain certain word (among other words in a cell)

[GRG Stevan]

Hello Guys,

Can anyone help me with this:

qqqqqq.JPG


Thanks!
Stevan

[63falcondude]

Try this:

=SUMIF(D:D,"*Industrial (Always On)*",E:E)

[avk]

In "A1" mark * before after : Like *Industrial (Always On)*
In "B2"

Please Login or Register  to view this content.

[jtakw]

Hi,

For your uploaded sample, in B1 use:

=SUMIF(D1:D1000,"*"&A1&"*",E1:E1000)

Change adjust cell references/ranges to suit your actual data.

[GRG Stevan]

Thank you so much, guys!
This works, thanks!

Stevan

[jtakw]

You're welcome, glad to help, thanks for the feedback.

[63falcondude]

Glad we could help. Thanks for the rep!

[GRG Stevan]

Hi Guys,

does anyone know if this work with sumproduct?
I think I will crash computer, trying over and over again and can't find why sumproduct can;t use this formula.
Can anyone confirm if this doesn't work with sumproduct or I'm doing something incorrectly?

Thanks,
Stevan

[63falcondude]

If you want to use SUMPRODUCT, you can do that like this:

=SUMPRODUCT((ISNUMBER(SEARCH(A1,D$1:D$4)))*(E$1:E$4))

[GRG Stevan]

Thanks a lot Falcon!
It works

I don't understand why sumproduct can't use the same formula format "*"&A1&"*" to pick up the cell that contains text from A1 cell as you guys showed me for sumif?

Anyway, thanks one more time!

[jtakw]

If the SUMIF formula does what you need, you should stick with the SUMIF as it's more efficient (less resource intensive) and faster than SUMPRODUCT.

[GRG Stevan]

Thanks Jtakw,

I needed additional thing (to pull data between a specific data range, for a cell whose text is contained in another range of cells).
I didn't know how to do this with sumif, but I guess there is a way.

Thanks for the advice!
S.