Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum without Range Parameters
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$1 50,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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum without Range Parameters
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum without Range Parameters
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. -- ** John C ** Please remember, if your question is answered, to check the YES box below. It helps everyone. "Peige414" wrote: 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$1 50,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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum without Range Parameters
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Peige414" wrote in message ... 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$1 50,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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum without Range Parameters
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 -- Biff Microsoft Excel MVP "Peige414" wrote in message ... 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$1 50,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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum without Range Parameters
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. "John C" wrote: 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. -- ** John C ** Please remember, if your question is answered, to check the YES box below. It helps everyone. "Peige414" wrote: 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$1 50,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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I sum a range using two parameters (eg location & date) | Excel Discussion (Misc queries) | |||
change parameters of a range to be summed | Excel Worksheet Functions | |||
conditional formula: sum a range if text present in another range | Excel Discussion (Misc queries) | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
HOW TO USE CONDITIONAL FORMATTING FROM ONE RANGE TO ANOTHER RANGE | Excel Worksheet Functions |