Looking for solution for using SUMIFS and perhaps a nested OR function ...

[Sunny54321]

Hi,

I wanted to ask if there is a way to use a SUMIFS with a criteria set that can “vary”???

Need to “SUMIFS” with multiple factors (DATE, ROOM NUMBER, ROOM USED FOR, etc) such that the ROOM IS USED for either ALL TYPES OF PROCEDURES(ELECTIVE, URGENT, EMERGENT, Add-On), ELECTIVE ONLY or NON-ELECTIVE(URGENT, EMERGENT, Add-On). Something like this: (C column is the reference array to be summed, E column is the Date, F is the Room Number, G is the VARYING field)

=SUMIFS($C5:$C10000,$E5:$E10000, $E$2,$F5:$F1000,$F$2,$G$5:$G$10000,$G$2)

It would be ideal if G2 can be an OR function that can be switched to contain 3 variations - OR(Elective,Urgent,Emergent,Add-on) or OR(Urgent,Emergent,Add-on) or Elective

Any insight or other method is welcome.

Thanks,
Sunny

 

[Jonmo1]

Try

=SUM(SUMIFS($C5:$C10000,$E5:$E10000, $E$2,$F5:$F1000,$F$2,$G$5:$G$10000,{"Elective","Urgent","Emergent","Add-on"}))

or
=SUMPRODUCT(SUMIFS($C5:$C10000,$E5:$E10000, $E$2,$F5:$F1000,$F$2,$G$5:$G$10000,$G$2:$G$5))
G2 = Elective
G3 = Urgent
G4 = Emergent
G5 = Add-on

 

[Sunny54321]

Hi, though the proposed solutions seems to be on the right track, the second one seems to be closer to what I want. BUT, it does not seem to "recalculate" if I blank out one of the criteria cells; as to limit the data range of the SUM. Any additional thoughts? thanks thus far!

 

[Jonmo1]

It works for me, but I noticed that one of the ranges was only 1000 while the others are 10000
This makes a difference in sumproduct..

Also, didn't notice before that the criteria ranges G2:G5 interesected with the actual range G5:G10000
Those should probably be moved outside of that range, to say J2:J5

Try
=SUMPRODUCT(SUMIFS($C5:$C10000,$E5:$E10000, $E$2,$F5:$F10000,$F$2,$G$5:$G$10000,$J$2:$J$5))

 

[Sunny54321]

REFERENCE:

Try
=SUMPRODUCT(SUMIFS($C5:$C10000,$E5:$E10000, $E$2,$F5:$F10000,$F$2,$G$5:$G$10000,$J$2:$J$5))

Hi,

Actually, those cell ref numbers (as above) were "dummy/fake numbers" for example sake. The actual cell ref numbers I use were okay (matched up) and did not overlap. For some reason, if I use the SUMPRODUCT(SUMIFS( approach, and try to delete one sub-criteria to narrow the calculation from FOUR subcriteria, it does not seem to "recalculate".

I'm trying to make it such that the user would select what "subcriteria" they are looking for and the sheet will recalculate a new output. In this scenario, should SUMPRODUCT work?

Thanks!
Sunny

 

[Jonmo1]

So are you saying...
It works at first, until you delete one of the criteria cells (J2:J5) ?
How exactly are you deleting? Just by clear contents, or actually right clicking - delete?
Do you have Calculation set to Automatic?

Anyway, it shouldn't matter if any of the criteria cells (J2:J5) are empty, it will still calculate for the other cells as well.

 

[Sunny54321]

I figured out the problem. The length of data does not seem like much but it must be. The raw data is about 16,000 lines long and 24 binary data points wide. Each line (24 data points wide - binary either 0 or 1 denoting if room is occupied or not in a 24 hr sched) ... each line occurs in a specific room for either Elective, Add On, Emergent or Urgent case. And each line is on a specific date. I am trying to SUM a cluster of rooms for each date to get a total.

The equation I was struggling with was to be placed in each hour to sum up a day's total for which ever type (Emergent, Urgent, Elective, Add On) and per each area. The equation that I was struggling with (AND THANKS TO YOUR HELP) will be used in EACH **** cell of a line and for a year of data.

Therefore, it was busy "calculating" and that is why it did not "update" quickly.

The method I am using seems to be equation heavy. I wish I could sum each line vs 1 equation per cell for each line (time many lines). I dont know of a way to make it more "efficient" to not tax the "processing" time.

I was going to copy the summation table into 3 sets total to run 3 graphs. Now I have 1 year summary table and it takes a long time to "compute".

Any recommendations?

Thanks
Sunny