SumIf Contains Question

[rjacmuto32]

I have some data that has items like Red Pen, Paper, Blue Pen etc. I want to do a SumIf based on contains a word like pen and i have this formula that works ok. =SUMIF(D4:D11,"*Pen*",E4:E11)

My issue I don't want to have a hard coded word in there like Pen. I want to have a drop down in Cell A1 that has items in it already (Say Pen, Marker, etc). This way someone selects the drop down and the totals update. This formula doens't work with the "" and cell A1. How can this be modified to work?

=SUMIF(D4:D11,"*A1*",E4:E11)

Thanks,
Robert

 

[Mike H..]

I have some data that has items like Red Pen, Paper, Blue Pen etc. I want to do a SumIf based on contains a word like pen and i have this formula that works ok. =SUMIF(D4:D11,"*Pen*",E4:E11)

My issue I don't want to have a hard coded word in there like Pen. I want to have a drop down in Cell A1 that has items in it already (Say Pen, Marker, etc). This way someone selects the drop down and the totals update. This formula doens't work with the "" and cell A1. How can this be modified to work?

=SUMIF(D4:D11,"*A1*",E4:E11)

Thanks,
Robert

Hi,

Try it like this

=SUMIF(D4:D11,"*" & A1 &"*",E4:E11)

 

[rjacmuto32]

That worked. I didn't think to break it up like that. Thanks.