CODEWITHSUNDEEP
excel
Rafael Osipov
Rafael Osipov

Reputation: 740

Excel Sumif With Some Conditions Based on String Values

I have a table in my Excel that has column named 'Text' (Column AJ). In that column I have few different sentences that contain the expression Quarterly 1, Quarterly 2 and on and on till 4. I want to sum the values in column AK (money values) based on the key words in the text column. I want to create some index table for each quarter that has the the some for each quarter.

note: sometimes its quarterly 1 and sometimes it's quarterly1

for Example the cells should look like this:

bla bla bla Quaterly 1 bla bla
bla bla bla Quaterly1 bla bla
bla bla bla Quaterly2 bla tatata
bla bla bla Quaterly 3 bla
Quaterly 4 bla bla

Someone have any intersting ideas to manipulate Excel?

Upvotes: 0

Views: 219

Answers (1)

Ames
Ames

Reputation: 470

Formula in E1: - credit to: shrivallabha.redij

=SUMPRODUCT((SUBSTITUTE(A:A," ","")=D1)+0,B:B)

enter image description here

Update for bla bla:

Since it will be contained within a sentence, I had to use search array on a control table. You can also achieve the end result with pivot.

Add a control column referencing to a control table in dotted line:

Array formula in C1: Ctrl+Shift+Enter to apply

{=INDEX(F$1:F$8,MATCH(TRUE,ISNUMBER(SEARCH(E$1:E$8,A1)),0))}

Formula in I1:

=SUMIF(C$1:C$9,H1,B$1:B$9)

enter image description here

Upvotes: 1

Related Questions