Conditional Sum without Range Parameters

[Peige414]

I'm trying to apply the concept of conditional summing to a dataset that
continually changes. I understand how to use conditional sum arrays when
specifiying a range, but since the dataset is always changing, I don't always
know what the new range will be. I don't want to waste time going through
all of my formulas fixing the ranges.

Right now I have something like this:
=SUM(IF($A$2:$A$150="Hospital
A",IF($B$2:$B$150="ICU",IF($F$2:$F$150=1,$H$2:$H$150,0),0),0))

Anyone know how I can phenagle things so that I can get excel to think this:
=SUM(IF($A:$A="Hospital A",IF($B:$B="ICU",IF($F:$F=1,$H:$H,0),0),0))

Any help would be greatly appreciated.

 

[TomPl]

This should work, but you cannot use "A:A" to reference a column with this
formula. Also, you could replace "Hospital A" with a reference to a cell
that has the value "Hospital A", etc.

=SUMPRODUCT(--((A1:A65000)="Hospital
A"),--((B1:B65000)="ICU"),--((F1:F65000)=1),H1:H65000)

Tom

 

[John C]

What do you mean the range continually changes? Do you mean it just gets
longer, or is there a possibility of different starting rows and ending rows
based on some other criteria.

If you do not have xl2007, you generally cannot use whole column references.
What you might try is this:
=SUMPRODUCT(--(INDIRECT("$A$2:$A$"&COUNTA($A:$A)+1)=J3),--(INDIRECT("$B$2:$B$"&COUNTA($A:$A)+1)=K3),--(INDIRECT("$C$2:$C$"&COUNTA($A:$A)+1)=1),($H$2:$H$29))

Note: I have the +1 after the COUNTA because I am assuming that you want to
check A2 through A? (and assuming contiguous cells) to see if data has been
entered into those cells. This is necessary if there is no header in A1. If,
however, there is header data in row 1, just don't add the +1.

 

[Bernard Liengme]

Firstly I would replace your initial formula by
=SUMPRODUCT(--($A$2:$A$150="Hospital
A"),--($B$2:$B$150="ICU"),--(F$2:$F$150=1),$H$2:$H$150)

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

This will also work if you use
=SUMPRODUCT(--($A$2:$A$2000="Hospital
A"),--($B$2:$B$2000="ICU"),--(F$2:$F$2000=1),$H$2:$H$2000)
even when some of the row are empty. So you might want to make the range a
large as you every expect the dataset to be.

I expect someone will show you how to set name ranges using OFFSET and COUNT
but I do not think it worth the trouble.

If you use Excel 2007, you can use full column references
=SUMPRODUCT(--(A:A="Hospital A"),--(B:B="ICU"),--(F:F=1),H:H)

best wishes

 

[T. Valko]

You can use a non-array entered formula to do this. Just use a big enough
range that you know you will never exceed:

All on one line

=SUMPROUCT(--(A2:A1000="Hospital A"),
--(B2:B1000="ICU"),--(F2:F1000=1),H2:H1000)

Or, you can use dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic

 

[Peige414]

I mean that the dataset just gets longer.

I'm not familiar with the indirect function in excel, so I think I'm going
to go with "$A$2:$A$65000". I was hoping there would be another way to do
this by just naming a column, but since I don't have excel 2007, I guess it's
not possible.

Thank you both for your help.