| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
and < for Array Sumif ({})
Hello Everyone
I am trying to do an Array Sumif formula for a discrete range. I can get it working no problem using <= but not for the other side I.e. I have columns of data on worksheet A, months (A), account codes (B) and values (C) This formula gives me all values for my account code (cell A11) up to and including June 2006 (200606) {=SUM(IF(A!$B$1:$B$9999=$A11,IF(A!$A$1:$A$9999<="2 00606",A!$C$1:$C$9999,0),0))} I need to be able to set up a formula that works for a specific range of months, for example April to June (200604 to 200606) I was hoping {=SUM(IF(A!$B$1:$B$9999=$A12,IF(AND(A!$A$1:$A$9999 ="2006004","200606"<=A!$A$1:$A$9999),A!$C$1:$C$99 99,0),0))} would work, but it doesn't. Does anyone know if this kind of formula is possible, and if so would they mind suggesting how the exact syntax should go? Thanks for your time so far, hope I hear from you soon |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
and < for Array Sumif ({})
You can try dynamic named ranges: http://www.ozgrid.com/Excel/DynamicRanges.htm
-- Brevity is the soul of wit. "Kiwi Matt" wrote: Hello Everyone I am trying to do an Array Sumif formula for a discrete range. I can get it working no problem using <= but not for the other side I.e. I have columns of data on worksheet A, months (A), account codes (B) and values (C) This formula gives me all values for my account code (cell A11) up to and including June 2006 (200606) {=SUM(IF(A!$B$1:$B$9999=$A11,IF(A!$A$1:$A$9999<="2 00606",A!$C$1:$C$9999,0),0))} I need to be able to set up a formula that works for a specific range of months, for example April to June (200604 to 200606) I was hoping {=SUM(IF(A!$B$1:$B$9999=$A12,IF(AND(A!$A$1:$A$9999 ="2006004","200606"<=A!$A$1:$A$9999),A!$C$1:$C$99 99,0),0))} would work, but it doesn't. Does anyone know if this kind of formula is possible, and if so would they mind suggesting how the exact syntax should go? Thanks for your time so far, hope I hear from you soon |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
and < for Array Sumif ({})
Thanks Dave
But I don't think this would work, the data is not sorted by month so wouldn't be in a continuous range Matt "Dave F" wrote: You can try dynamic named ranges: http://www.ozgrid.com/Excel/DynamicRanges.htm -- Brevity is the soul of wit. "Kiwi Matt" wrote: Hello Everyone I am trying to do an Array Sumif formula for a discrete range. I can get it working no problem using <= but not for the other side I.e. I have columns of data on worksheet A, months (A), account codes (B) and values (C) This formula gives me all values for my account code (cell A11) up to and including June 2006 (200606) {=SUM(IF(A!$B$1:$B$9999=$A11,IF(A!$A$1:$A$9999<="2 00606",A!$C$1:$C$9999,0),0))} I need to be able to set up a formula that works for a specific range of months, for example April to June (200604 to 200606) I was hoping {=SUM(IF(A!$B$1:$B$9999=$A12,IF(AND(A!$A$1:$A$9999 ="2006004","200606"<=A!$A$1:$A$9999),A!$C$1:$C$99 99,0),0))} would work, but it doesn't. Does anyone know if this kind of formula is possible, and if so would they mind suggesting how the exact syntax should go? Thanks for your time so far, hope I hear from you soon |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
and < for Array Sumif ({})
Matt,
the formula Bernie gave you does not require sorting. All it requires is that your data do not exceed row 9999 HTH Kostis Vezerides Kiwi Matt wrote: Thanks Dave But I don't think this would work, the data is not sorted by month so wouldn't be in a continuous range Matt "Dave F" wrote: You can try dynamic named ranges: http://www.ozgrid.com/Excel/DynamicRanges.htm -- Brevity is the soul of wit. "Kiwi Matt" wrote: Hello Everyone I am trying to do an Array Sumif formula for a discrete range. I can get it working no problem using <= but not for the other side I.e. I have columns of data on worksheet A, months (A), account codes (B) and values (C) This formula gives me all values for my account code (cell A11) up to and including June 2006 (200606) {=SUM(IF(A!$B$1:$B$9999=$A11,IF(A!$A$1:$A$9999<="2 00606",A!$C$1:$C$9999,0),0))} I need to be able to set up a formula that works for a specific range of months, for example April to June (200604 to 200606) I was hoping {=SUM(IF(A!$B$1:$B$9999=$A12,IF(AND(A!$A$1:$A$9999 ="2006004","200606"<=A!$A$1:$A$9999),A!$C$1:$C$99 99,0),0))} would work, but it doesn't. Does anyone know if this kind of formula is possible, and if so would they mind suggesting how the exact syntax should go? Thanks for your time so far, hope I hear from you soon |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
and < for Array Sumif ({})
Matt,
Try: =SUMPRODUCT((A!$B$1:$B$9999=$A11)*(A!$A$1:$A$9999 =200604)*(A!$A$1:$A$9999<=200606)*(A!$C$1:$C$9999) ) and if the date values are strings and not numbers: =SUMPRODUCT((A!$B$1:$B$9999=$F6)*(VALUE(A!$A$1:$A$ 9999)=200604)*(VALUE(A!$A$1:$A$9999)<=200606)*(A! $C$1:$C$9999)) HTH, Bernie MS Excel MVP "Kiwi Matt" wrote in message ... Hello Everyone I am trying to do an Array Sumif formula for a discrete range. I can get it working no problem using <= but not for the other side I.e. I have columns of data on worksheet A, months (A), account codes (B) and values (C) This formula gives me all values for my account code (cell A11) up to and including June 2006 (200606) {=SUM(IF(A!$B$1:$B$9999=$A11,IF(A!$A$1:$A$9999<="2 00606",A!$C$1:$C$9999,0),0))} I need to be able to set up a formula that works for a specific range of months, for example April to June (200604 to 200606) I was hoping {=SUM(IF(A!$B$1:$B$9999=$A12,IF(AND(A!$A$1:$A$9999 ="2006004","200606"<=A!$A$1:$A$9999),A!$C$1:$C$99 99,0),0))} would work, but it doesn't. Does anyone know if this kind of formula is possible, and if so would they mind suggesting how the exact syntax should go? Thanks for your time so far, hope I hear from you soon |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
and < for Array Sumif ({})
Hi Bernie
That does the trick Thank you very much indeed "Bernie Deitrick" wrote: Matt, Try: =SUMPRODUCT((A!$B$1:$B$9999=$A11)*(A!$A$1:$A$9999 =200604)*(A!$A$1:$A$9999<=200606)*(A!$C$1:$C$9999) ) and if the date values are strings and not numbers: =SUMPRODUCT((A!$B$1:$B$9999=$F6)*(VALUE(A!$A$1:$A$ 9999)=200604)*(VALUE(A!$A$1:$A$9999)<=200606)*(A! $C$1:$C$9999)) HTH, Bernie MS Excel MVP "Kiwi Matt" wrote in message ... Hello Everyone I am trying to do an Array Sumif formula for a discrete range. I can get it working no problem using <= but not for the other side I.e. I have columns of data on worksheet A, months (A), account codes (B) and values (C) This formula gives me all values for my account code (cell A11) up to and including June 2006 (200606) {=SUM(IF(A!$B$1:$B$9999=$A11,IF(A!$A$1:$A$9999<="2 00606",A!$C$1:$C$9999,0),0))} I need to be able to set up a formula that works for a specific range of months, for example April to June (200604 to 200606) I was hoping {=SUM(IF(A!$B$1:$B$9999=$A12,IF(AND(A!$A$1:$A$9999 ="2006004","200606"<=A!$A$1:$A$9999),A!$C$1:$C$99 99,0),0))} would work, but it doesn't. Does anyone know if this kind of formula is possible, and if so would they mind suggesting how the exact syntax should go? Thanks for your time so far, hope I hear from you soon |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
and < for Array Sumif ({})
Matt,
You're welcome - Glad to hear it! Bernie MS Excel MVP That does the trick Thank you very much indeed |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
