Searching multiple columns AND rows for a value, and summing data from that row in ONE column that matches

[jfgoodhew1]

Hello lucky people, I have a Friday Afternoon style question...

I have panels, which are linked to frames. Different panels are linked to different frames.
I need to find the overall frame heights, by summing all panel heights which are linked to that frame.
I had it all working when the groups of frames each panel referenced were contiguous... Now, they're not

So, my sample data:

Panel height Panel width Frame # Frame # Frame #
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]400[/TD]
[TD]700[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]730[/TD]
[TD]700[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]730[/TD]
[TD]700[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD]850[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]730[/TD]
[TD]850[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]

I want to:

- Find all instances of 1 from range C1:E5
- Sum the values in A1:A5 accordingly

Sounds dead simple, right?


I managed to find the rail width using an *array* formula below (entered by CTRL+SHIFT+ENTER). This works because the width is the same throughout the frame, so the MIN() part works.
This was from Aladin Ayurek here (with extra for insert rows robustness):
=IF(COUNTIF(B2:F8,O1),INDEX(A2:A8,MIN(IF(B2:F8=O1,ROW(A2:A8)-ROW(A2)+1))),"")
https://www.mrexcel.com/forum/excel-questions/562451-find-value-across-multiple-columns-2.html

The height of the frame, however, is a different game because I need to sum all those instances in A1:A5 - preferably without VBA for reasons that exist. I feel like we are ever so close but missing something vital.

Thanks for all your help!

 

[jfgoodhew1]

I have resolved it by Sum(SumIf(A1:A5,C1:C5,C1)+SumIf(A1:A5,D1:D5,C1)+SumIf(A1:A5,E1:E5,C1))
It's a temporary fix, obviously in VBA you can name a range as RefFrames, and
For Each Column in RefFrames SumIf.....

But outside of VBA this is working for now.

 

[jfgoodhew1]

I clearly typed that far too fast... Replace + with comma:
Sum(SumIf(A1:A5,C1:C5,C1),SumIf(A1:A5,D1:D5,C1),SumIf(A1:A5,E1:E5,C1))

It's a horrible formula going all the way out to AZ1:AZ100, but it works. If anyone has a better way, please do comment!

The intended output of the formula for frame 1 is 400 + 730 + 730 = 1460 in the sample data.

 

[Aladin Akyurek]

Looks like: Control+shift+enter, not just enter...

=SUM(IF(C1:E5=1,A1:A5))

But, what is, we have 1 occurring in the first row not once but twice? Are we going to sum 400 twice?

Note. I think you meant SUMIFS, not SUMIF, in the formula you posted, which also requires control+shift+enter.

 

[jfgoodhew1]

Looks like: Control+shift+enter, not just enter...

=SUM(IF(C1:E5=1,A1:A5))

Aladin Akyurek you absolute legend. I was wondering about array formulae, never used them before on my own - I only used one once. Naturally I've used arrays in VBA but... Nothing that elegant.

That is the desired result!

If we have Frame 1 occurring twice in one row (meaning two panels with same size on Frame 1), it means we will add that panel's height twice. This actually does work correctly in your suggestion, quite by accident. Amazing result!

 

[Aladin Akyurek]

@jfgoodhew1

That's great. Thanks for the kind update.