Excel - SUMIFS() works with one range, but doesn't with another nearly identical range 2

[PSchubert]

Hi Techies,

I have an Excel spreadsheet that tracks hourly temperature and relative humidity for Level 1 and Level 2. It uses averages, and minimum and maximum values.

I've attached it to make it easier to follow my issue.

In the range B12:AW12, I've averaged the readings for temperature and relative humidity for each level, each day.

In cell AX12, I've averaged the combined temperature averages for Level 1 with this formula:

=SUM(SUMIFS(B12:AW12,B2:AW2,"Level 1",B3:AW3,"C°")/COUNTIFS(B2:AW2,"Level 1",B3:AW3,"C°",B12:AW12,"<>0"))

It works great!

In cell AY12, I want to average the combined humidity averages for Level 1. I can't even get the SUMIFS part to return anything except 0.0, when the syntax for it is practically identical to the SUMIFS in AX12 that works:

=SUMIFS(B12:AW12,B2:AW2,"Level 1",B3:AW3,"RH%")

It should return 202.9, but all I get is a big, fat 0.0.

Could someone smarter than me please lend a hand? I'd be ever so grateful!

Thank you!

 

[Andrzejek]

Your cell B2 is a merged cell (with C2) so your first formula for C° works OK because Excel compares B2 and C2 and finds C°
When you look for RH% - you cannot find it because there is no RH% when you compare B2 to C2

Unmerge cells in row 2, make them

and your new formula will work.

BTW - your formula in B12 could be simply:
[tt]=IFERROR(AVERAGE(B4:B11), 0)[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

In cell AY12, I want to average the combined humidity averages for Level 1. I can't even get the SUMIFS part to return anything except 0.0,

You can, mind that for merged cells, as B2 and C2 in your file, C2 is empty and you still can reference it. For this data structure you have to look at B1 for level, so just shift left the range in second argument:

[tt]=SUMIFS(B12:AW12,A2:AV2,"Level 1",B3:AW3,"RH%")[/tt]

combo

 

[Andrzejek]

...average the combined humidity averages...

Taking an Average of Averages – What Could Go Wrong

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[PSchubert]

Thanks for your replies, Andrzejek and Combo!

Andrzejek's solution worked very well. I couldn't get Combo's to; probably my ineptitude.

 

[mintjulep]

If you normalize your data then you could do anything you want (except averages of averages) with a few clicks in a pivot table.

faq68-7103

 

[Andrzejek]

I couldn't get Combo's [formula] to [work]; probably my ineptitude.

just shift left the range in [highlight #FCE94F]second argument[/highlight]

In cell AY12:
[tt]=SUMIFS(B12:AW12,[highlight #FCE94F]B[/highlight]2:[highlight #FCE94F]AW[/highlight]2,"Level 1",B3:AW3,"RH%")[/tt] <- your formula
[tt]=SUMIFS(B12:AW12,[highlight #FCE94F]A[/highlight]2:[highlight #FCE94F]AV[/highlight]2,"Level 1",B3:AW3,"RH%")[/tt] <- Combo's formula

Just copy Combo's formula, place it in cell AY12 and you will get your 202.9


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson