Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

PSchubert

Technical User
Jun 6, 2006
50
AU
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:

Code:
=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:

Code:
=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!
 
 https://files.engineering.com/getfile.aspx?folder=596dabf8-97b8-4b85-bfb1-991f33d743ea&file=Hygrothermograph_Data.xlsx
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
Cells_owtnxs.png


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
 
PSchubert said:
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
 
Thanks for your replies, Andrzejek and Combo!

Andrzejek's solution worked very well. I couldn't get Combo's to; probably my ineptitude.
 
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
 
PSchubert said:
I couldn't get Combo's [formula] to [work]; probably my ineptitude.

combo said:
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
[wiggle]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top