SumIf like formula for horizontal data where your value has to search within a text string

[marilyn123]

Hi,

I hope somebody is able to help with the below. I've been given a report with a bunch of horizontal data (changing the entire report structure is not really an option at this point). In the column where it says "Language 1" I need a formula that can search the first row of all the columns for the word "language 1" and sum a certain row value, similar to what a sumif does. So in this example it needs to sum up the two values under 'Language 1 (only)' and 'Language 1 Hispanic', etc. There will be a lot of other languages in different columns but of course it would have been too big to show for here.

So for example the first row of the data under language 1 is 0 (0+0) and the second is 1 (1+0). I came up with a horizontal lookup that will bring the first value it finds, but can't seem to figure out something that will find all of the values sum all of it together. =HLOOKUP("*" & F2 & "*",$G$2:$U$8,ROW()-1,FALSE). The first part of the formula is so that it will agree with anything that has the word "Arabic", the Row()-1 is so it will bring back the appropriate row value.

Any advice on how to get these values to sum? Much appreciated...
I hope this all makes sense!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 454"]
<tbody>[TR]
[TD="class: xl66, width: 65, bgcolor: transparent"]Program
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Location
[/TD]
[TD="class: xl66, width: 65, bgcolor: transparent"]Total
[/TD]
[TD="class: xl67, width: 65, bgcolor: yellow"]Language 1
[/TD]
[TD="class: xl67, width: 65, bgcolor: yellow"]Total %
[/TD]
[TD="class: xl66, width: 65, bgcolor: transparent"]Language 1(only)
[/TD]
[TD="class: xl66, width: 80, bgcolor: transparent"]%
[/TD]
[TD="class: xl66, width: 65, bgcolor: transparent"]Language 1, Hispanic
[/TD]
[TD="class: xl66, width: 69, bgcolor: transparent"]%
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]68
[/TD]
[TD="class: xl71, width: 65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl72, width: 65, bgcolor: yellow, align: right"]0.0%
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl73, width: 80, bgcolor: transparent, align: right"]0.00 %
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl73, width: 69, bgcolor: transparent, align: right"]0.00 %
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl71, width: 65, bgcolor: yellow, align: right"]1
[/TD]
[TD="class: xl72, width: 65, bgcolor: yellow, align: right"]25.0%
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl73, width: 80, bgcolor: transparent, align: right"]25.00 %
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl73, width: 69, bgcolor: transparent, align: right"]0.00 %
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]C
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]135
[/TD]
[TD="class: xl71, width: 65, bgcolor: yellow, align: right"]1
[/TD]
[TD="class: xl72, width: 65, bgcolor: yellow, align: right"]0.7%
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl73, width: 80, bgcolor: transparent, align: right"]0.74 %
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl73, width: 69, bgcolor: transparent, align: right"]0.00 %
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]D
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]118
[/TD]
[TD="class: xl71, width: 65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl72, width: 65, bgcolor: yellow, align: right"]0.0%
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl73, width: 80, bgcolor: transparent, align: right"]0.00 %
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl73, width: 69, bgcolor: transparent, align: right"]0.00 %
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]E
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]125
[/TD]
[TD="class: xl71, width: 65, bgcolor: yellow, align: right"]3
[/TD]
[TD="class: xl72, width: 65, bgcolor: yellow, align: right"]2.4%
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl73, width: 80, bgcolor: transparent, align: right"]1.60 %
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl73, width: 69, bgcolor: transparent, align: right"]0.00 %
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]F
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]705
[/TD]
[TD="class: xl71, width: 65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl72, width: 65, bgcolor: yellow, align: right"]0.0%
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl73, width: 80, bgcolor: transparent, align: right"]0.00 %
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl73, width: 69, bgcolor: transparent, align: right"]0.00 %
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dreid1011]

Why is the 5th row total 3? Is it not 2? And if I understand correctly, you just want to sum each row separately for any column containing "Language 1"?

 

[marilyn123]

Why is the 5th row total 3? Is it not 2? And if I understand correctly, you just want to sum each row separately for any column containing "Language 1"?

Hi, you are correct in that it should say 2. It's possible I left out a row from the actual spreadsheet. But your logic is correct.

 

[dreid1011]

Hi, you are correct in that it should say 2. It's possible I left out a row from the actual spreadsheet. But your logic is correct.

Ok, assuming your data starts in A1, then put this in your Language 1 column (D2?) and copy down. Adjust the F1:I1 and F2:I2 ranges to suit your data.

=SUMPRODUCT((IFERROR(SEARCH("Language 1",$F$1:$I$1),0))*--(F2:I2>0)*(F2:I2))

This is an array formula entered with CTRL+SHIFT+ENTER, not just ENTER.

 

[marilyn123]

Hi,

The formula works which is great. Now that I've tested it out, I'm finding another problem. I need the word "Language 1" to be the FIRST word in the text string. So for this example, I'd change Language 1 to "LanguageA" to make it easier. One word with no numbers in it.

The reason for this is because - for example - if I want to sum the values from ["Arabic", "Arabic, Hispanic", "Arabic, Other"] but do NOT want to pull "Farsi, Arabic" as that is under the category of Farsi. I hope this makes sense.

 

[dreid1011]

Hi,

The formula works which is great. Now that I've tested it out, I'm finding another problem. I need the word "Language 1" to be the FIRST word in the text string. So for this example, I'd change Language 1 to "LanguageA" to make it easier. One word with no numbers in it.

The reason for this is because - for example - if I want to sum the values from ["Arabic", "Arabic, Hispanic", "Arabic, Other"] but do NOT want to pull "Farsi, Arabic" as that is under the category of Farsi. I hope this makes sense.

Ok. Will the category always be the first word then? And before the comma if there is a comma?

 

[marilyn123]

Yes to both of your questions

 

[dreid1011]

Yes to both of your questions

Ok, give this a try:

=SUMPRODUCT(--(LEFT($F$1:$I$1,LEN(TRIM($D$1)))=TRIM($D$1))*--(F2:I2>0)*(F2:I2))

This is an array formula entered with CTRL+SHIFT+ENTER, not just ENTER.

Also, it assumes the Category you want to sum is in D1.

 

[marilyn123]

Ok, give this a try:

=SUMPRODUCT(--(LEFT($F$1:$I$1,LEN(TRIM($D$1)))=TRIM($D$1))*--(F2:I2>0)*(F2:I2))

This is an array formula entered with CTRL+SHIFT+ENTER, not just ENTER.

Also, it assumes the Category you want to sum is in D1.

Seems to be working great. I'm really impressed!

 

[dreid1011]

Seems to be working great. I'm really impressed!

Glad it's working for you, and happy to help.