Conditional Sum in multi column multi row range

[sriche01]

Hello All,

I am trying to get the sum within a range based on conditions both vertically and horizontally.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2 YR[/TD]
[TD]Available[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]Fall[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]Fall[/TD]
[/TR]
[TR]
[TD]ABC1[/TD]
[TD]0[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD]54[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]126[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]36[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]ABC2[/TD]
[TD]0[/TD]
[TD]42[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]810[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]18[/TD]
[TD]72[/TD]
[/TR]
[TR]
[TD]DEF1[/TD]
[TD]0[/TD]
[TD]126[/TD]
[TD]0[/TD]
[TD]162[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]54[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]GHI1[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]180[/TD]
[TD]0[/TD]
[TD]306[/TD]
[TD]36[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]GHI2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]270[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]126[/TD]
[/TR]
[TR]
[TD]GHI3[/TD]
[TD]0[/TD]
[TD]176[/TD]
[TD]16[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]126[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]90[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]JKL1[/TD]
[TD]18[/TD]
[TD]126[/TD]
[TD]36[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]144[/TD]
[TD]0[/TD]
[TD]720[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]108[/TD]
[/TR]
[TR]
[TD]JKL2[/TD]
[TD]0[/TD]
[TD]1476[/TD]
[TD]810[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]900[/TD]
[TD]0[/TD]
[TD]90[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

The above is a sample of data from an availability list.

• The first column is a set of item numbers.
• Item numbers consist of several letters (indicates kind) followed by numbers (indicates size)
• Combinations of kind and size are unique, but any one kind can have 1 or multiple sizes
• The calendar headings are revolving time frames. "2YR" is static, "Available" is always the current month or time frame, and they are consecutive after that.
• There is a hidden column between each of the columns above that I do not want to sum. It does not have a year heading.

On another sheet, I have a list of Item prefixes (e.g. ABC, DEF, GHI etc.). These indicate kind regardless of size.

Bottom line
I want to sum the numbers available for each Item prefix (all sizes - so totaling all ABC1 and ABC2 on the ABC line) when the year is the current year. So I need a formula to sum the rows that start with the right prefix, but only the columns that say 2017. Keep in mind those columns change. When we get to May, the number of columns headed with 2017 reduces by 1 and those headed with 2018 increases by 1. However, they stay in chronological order. Any Ideas? Thank you in advance!

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(SEARCH(X2&"|",$A$3:$A$10&"|")),IF($B$1:$N$1=Y$1,$B$3:$N$10)))

where X2 = ABC and Y1 = 2017.

 

[sriche01]

Thank you! Worked with the exception of the first &"|". Changed to "*" and worked great. Thank you again!

 

[Aladin Akyurek]

Thank you! Worked with the exception of the first &"|". Changed to "*" and worked great. Thank you again!

Right. I had actually this in mind:

=SUM(IF(ISNUMBER(SEARCH("|"&X2,"|"&$A$3:$A$10)),IF($B$1:$N$1=Y$1,$B$3:$N$10)))