|
|
|
|
|
| |
 Answer |
|
| |
=SUMIF(Base_Date,">="&X2,Base_Amount)-SUMIF(Base_Date,">"&Y2,Base_Amount)
where X2 houses the first day date of the month/year of interset like:
03/01/2005 and Y2 one of:
03/31/2005
=DATE(YEAR(X2),MONTH(X2)+1,0)
SleazyBreezy wrote:
> Hi all,
>
> Say I have a spreadsheet listed as follows:
>
> A1 = 03/31/05
> A2 = $5.00
>
> B1 = 03/31/05
> B2 = $10.00
>
> C1 = 04/01/05
> C2 = $20.00
>
> The entire A column is named "Base_Date" and the entire B column is named
> "Base_Amount".
>
> I would like a formula that will sum values in the Amount column that
> correspond with a specific date range, ie. March only. This formula must
> reside in a different worksheet (within the same workbook).
>
> I've tried the following:
>
> =SUM(IF(Base_Date>=DATEVALUE("03/01/2005"),IF(Base_Date<=DATEVALUE("03/31/2005"),Base_Amount,0),0))
>
> If I place the formula in a seperate worksheet from the data (as intended),
> its value is $35.00 (adds all values in column B) when I'm expecting it to be
> only $15.00.
>
> If I place the formula in the same worksheet as the data (not in columns A
> or B), its value is reported as 0.
>
> I'm sure I'm missing something miniscule here. Any suggestions?
>
> TIA,
> Sleazy
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
Thank you very much! Thankfully, my date criteria is static, so the X2/Y2
variables are not needed. I used your formula to achieve the desired result
by moving the closing quotes as follows:
=SUMIF(Base_Date,">=03/01/2005",Base_Amount)-SUMIF(Base_Date,">03/31/2005",Base_Amount)
"Aladin Akyurek" wrote:
> =SUMIF(Base_Date,">="&X2,Base_Amount)-SUMIF(Base_Date,">"&Y2,Base_Amount)
>
> where X2 houses the first day date of the month/year of interset like:
> 03/01/2005 and Y2 one of:
>
> 03/31/2005
>
> =DATE(YEAR(X2),MONTH(X2)+1,0)
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Okay, now I have a second, similar problem. Here's my new table:
A1: 03/01/2005
B1: $5.00
A2: 03/05/2005
B2: $12.00
A3: 04/05/2005
B3: $3.00
A4: 03/05/2005
B4: $50.00
As before, the A column is named Base_Date and the B column is named
Base_Amount. I want to modify the SUMIF formula, as used below, so that it
adds only the records associated with the *latest* date in a specified date
range. Referencing the table above, if I specify >03/01/2005 and
<=03/31/2005, the formula should add B2+B4, and not include B1. However, if I
then add the following to the table:
A5: 03/29/2005
B5: $1.00
The formula should only report back B5 for its value, as A5 is the latest
date in the allowed range.
Thanks again in advance.
:-)
- Sleazy
"SleazyBreezy" wrote:
> Thank you very much! Thankfully, my date criteria is static, so the X2/Y2
> variables are not needed. I used your formula to achieve the desired result
> by moving the closing quotes as follows:
>
> =SUMIF(Base_Date,">=03/01/2005",Base_Amount)-SUMIF(Base_Date,">03/31/2005",Base_Amount) |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
D2: 3/1/05
E2: 3/31/05
F2:
=MAX(IF(($A$1:$A$4>=D2)*($A$1:$A$4<=E2),$A$1:$A$4))
which must be confirmed with control+shift+enter instead of just with enter.
G2:
=SUMIF($A$1:$A$4,F2,$B$1:$B$4)
SleazyBreezy wrote:
> Okay, now I have a second, similar problem. Here's my new table:
>
> A1: 03/01/2005
> B1: $5.00
> A2: 03/05/2005
> B2: $12.00
> A3: 04/05/2005
> B3: $3.00
> A4: 03/05/2005
> B4: $50.00
>
> As before, the A column is named Base_Date and the B column is named
> Base_Amount. I want to modify the SUMIF formula, as used below, so that it
> adds only the records associated with the *latest* date in a specified date
> range. Referencing the table above, if I specify >03/01/2005 and
> <=03/31/2005, the formula should add B2+B4, and not include B1. However, if I
> then add the following to the table:
>
> A5: 03/29/2005
> B5: $1.00
>
> The formula should only report back B5 for its value, as A5 is the latest
> date in the allowed range.
>
> Thanks again in advance.
>
> :-)
>
> - Sleazy
>
> "SleazyBreezy" wrote:
>
>
>>Thank you very much! Thankfully, my date criteria is static, so the X2/Y2
>>variables are not needed. I used your formula to achieve the desired result
>>by moving the closing quotes as follows:
>>
>>=SUMIF(Base_Date,">=03/01/2005",Base_Amount)-SUMIF(Base_Date,">03/31/2005",Base_Amount)
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
This really works well. Thanks so much!
It's humbling to know that I just can't grasp the concept of array formulas
at this point of my Excel training. Back to the books!
- Sleazy
"Aladin Akyurek" wrote:
> D2: 3/1/05
> E2: 3/31/05
> F2:
>
> =MAX(IF(($A$1:$A$4>=D2)*($A$1:$A$4<=E2),$A$1:$A$4))
>
> which must be confirmed with control+shift+enter instead of just with enter.
>
> G2:
>
> =SUMIF($A$1:$A$4,F2,$B$1:$B$4)
>
> SleazyBreezy wrote:
> > Okay, now I have a second, similar problem. Here's my new table:
> >
> > A1: 03/01/2005
> > B1: $5.00
> > A2: 03/05/2005
> > B2: $12.00
> > A3: 04/05/2005
> > B3: $3.00
> > A4: 03/05/2005
> > B4: $50.00
> >
> > As before, the A column is named Base_Date and the B column is named
> > Base_Amount. I want to modify the SUMIF formula, as used below, so that it
> > adds only the records associated with the *latest* date in a specified date
> > range. Referencing the table above, if I specify >03/01/2005 and
> > <=03/31/2005, the formula should add B2+B4, and not include B1. However, if I
> > then add the following to the table:
> >
> > A5: 03/29/2005
> > B5: $1.00
> >
> > The formula should only report back B5 for its value, as A5 is the latest
> > date in the allowed range.
> >
> > Thanks again in advance.
> >
> > :-)
> >
> > - Sleazy
> >
> > "SleazyBreezy" wrote:
> >
> >
> >>Thank you very much! Thankfully, my date criteria is static, so the X2/Y2
> >>variables are not needed. I used your formula to achieve the desired result
> >>by moving the closing quotes as follows:
> >>
> >>=SUMIF(Base_Date,">=03/01/2005",Base_Amount)-SUMIF(Base_Date,">03/31/2005",Base_Amount)
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
I tried this formula and I can not seem to make it work. Is the MAX IF
formula supposed to generate a zero as a result?
"Aladin Akyurek" wrote:
> D2: 3/1/05
> E2: 3/31/05
> F2:
>
> =MAX(IF(($A$1:$A$4>=D2)*($A$1:$A$4<=E2),$A$1:$A$4))
>
> which must be confirmed with control+shift+enter instead of just with enter.
>
> G2:
>
> =SUMIF($A$1:$A$4,F2,$B$1:$B$4)
>
> SleazyBreezy wrote:
> > Okay, now I have a second, similar problem. Here's my new table:
> >
> > A1: 03/01/2005
> > B1: $5.00
> > A2: 03/05/2005
> > B2: $12.00
> > A3: 04/05/2005
> > B3: $3.00
> > A4: 03/05/2005
> > B4: $50.00
> >
> > As before, the A column is named Base_Date and the B column is named
> > Base_Amount. I want to modify the SUMIF formula, as used below, so that it
> > adds only the records associated with the *latest* date in a specified date
> > range. Referencing the table above, if I specify >03/01/2005 and
> > <=03/31/2005, the formula should add B2+B4, and not include B1. However, if I
> > then add the following to the table:
> >
> > A5: 03/29/2005
> > B5: $1.00
> >
> > The formula should only report back B5 for its value, as A5 is the latest
> > date in the allowed range.
> >
> > Thanks again in advance.
> >
> > :-)
> >
> > - Sleazy
> >
> > "SleazyBreezy" wrote:
> >
> >
> >>Thank you very much! Thankfully, my date criteria is static, so the X2/Y2
> >>variables are not needed. I used your formula to achieve the desired result
> >>by moving the closing quotes as follows:
> >>
> >>=SUMIF(Base_Date,">=03/01/2005",Base_Amount)-SUMIF(Base_Date,">03/31/2005",Base_Amount)
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Did you enter the formula using Control+Shift+Enter or just the Enter key by
itself? The formula requires the 3-key combination to work correctly.
Rick
"nukegirl" <nukegirl@discussions.microsoft.com> wrote in message
news:518E0A58-5DDD-422A-8F7B-71803CA66EDF@microsoft.com...
>I tried this formula and I can not seem to make it work. Is the MAX IF
> formula supposed to generate a zero as a result?
>
>
>
> "Aladin Akyurek" wrote:
>
>> D2: 3/1/05
>> E2: 3/31/05
>> F2:
>>
>> =MAX(IF(($A$1:$A$4>=D2)*($A$1:$A$4<=E2),$A$1:$A$4))
>>
>> which must be confirmed with control+shift+enter instead of just with
>> enter.
>>
>> G2:
>>
>> =SUMIF($A$1:$A$4,F2,$B$1:$B$4)
>>
>> SleazyBreezy wrote:
>> > Okay, now I have a second, similar problem. Here's my new table:
>> >
>> > A1: 03/01/2005
>> > B1: $5.00
>> > A2: 03/05/2005
>> > B2: $12.00
>> > A3: 04/05/2005
>> > B3: $3.00
>> > A4: 03/05/2005
>> > B4: $50.00
>> >
>> > As before, the A column is named Base_Date and the B column is named
>> > Base_Amount. I want to modify the SUMIF formula, as used below, so that
>> > it
>> > adds only the records associated with the *latest* date in a specified
>> > date
>> > range. Referencing the table above, if I specify >03/01/2005 and
>> > <=03/31/2005, the formula should add B2+B4, and not include B1.
>> > However, if I
>> > then add the following to the table:
>> >
>> > A5: 03/29/2005
>> > B5: $1.00
>> >
>> > The formula should only report back B5 for its value, as A5 is the
>> > latest
>> > date in the allowed range.
>> >
>> > Thanks again in advance.
>> >
>> > :-)
>> >
>> > - Sleazy
>> >
>> > "SleazyBreezy" wrote:
>> >
>> >
>> >>Thank you very much! Thankfully, my date criteria is static, so the
>> >>X2/Y2
>> >>variables are not needed. I used your formula to achieve the desired
>> >>result
>> >>by moving the closing quotes as follows:
>> >>
>> >>=SUMIF(Base_Date,">=03/01/2005",Base_Amount)-SUMIF(Base_Date,">03/31/2005",Base_Amount)
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Thanks Rick! Got it to work. Appreciate the response.
"Rick Rothstein (MVP - VB)" wrote:
> Did you enter the formula using Control+Shift+Enter or just the Enter key by
> itself? The formula requires the 3-key combination to work correctly.
>
> Rick
>
>
> "nukegirl" <nukegirl@discussions.microsoft.com> wrote in message
> news:518E0A58-5DDD-422A-8F7B-71803CA66EDF@microsoft.com...
> >I tried this formula and I can not seem to make it work. Is the MAX IF
> > formula supposed to generate a zero as a result?
> >
> >
> >
> > "Aladin Akyurek" wrote:
> >
> >> D2: 3/1/05
> >> E2: 3/31/05
> >> F2:
> >>
> >> =MAX(IF(($A$1:$A$4>=D2)*($A$1:$A$4<=E2),$A$1:$A$4))
> >>
> >> which must be confirmed with control+shift+enter instead of just with
> >> enter.
> >>
> >> G2:
> >>
> >> =SUMIF($A$1:$A$4,F2,$B$1:$B$4)
> >>
> >> SleazyBreezy wrote:
> >> > Okay, now I have a second, similar problem. Here's my new table:
> >> >
> >> > A1: 03/01/2005
> >> > B1: $5.00
> >> > A2: 03/05/2005
> >> > B2: $12.00
> >> > A3: 04/05/2005
> >> > B3: $3.00
> >> > A4: 03/05/2005
> >> > B4: $50.00
> >> >
> >> > As before, the A column is named Base_Date and the B column is named
> >> > Base_Amount. I want to modify the SUMIF formula, as used below, so that
> >> > it
> >> > adds only the records associated with the *latest* date in a specified
> >> > date
> >> > range. Referencing the table above, if I specify >03/01/2005 and
> >> > <=03/31/2005, the formula should add B2+B4, and not include B1.
> >> > However, if I
> >> > then add the following to the table:
> >> >
> >> > A5: 03/29/2005
> >> > B5: $1.00
> >> >
> >> > The formula should only report back B5 for its value, as A5 is the
> >> > latest
> >> > date in the allowed range.
> >> >
> >> > Thanks again in advance.
> >> >
> >> > :-)
> >> >
> >> > - Sleazy
> >> >
> >> > "SleazyBreezy" wrote:
> >> >
> >> >
> >> >>Thank you very much! Thankfully, my date criteria is static, so the
> >> >>X2/Y2
> >> >>variables are not needed. I used your formula to achieve the desired
> >> >>result
> >> >>by moving the closing quotes as follows:
> >> >>
> >> >>=SUMIF(Base_Date,">=03/01/2005",Base_Amount)-SUMIF(Base_Date,">03/31/2005",Base_Amount)
> >>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Hi
I have a table where there are multiple entries for the same month and I
want to produce monthly totals. I used the formula above (the MAX IF is
working great) but the SUM IF is only returning one of the amounts per month
rather than adding the different entries. Can anyone help?
Thank you in advance!
"Rick Rothstein (MVP - VB)" wrote:
> Did you enter the formula using Control+Shift+Enter or just the Enter key by
> itself? The formula requires the 3-key combination to work correctly.
>
> Rick
>
>
> "nukegirl" <nukegirl@discussions.microsoft.com> wrote in message
> news:518E0A58-5DDD-422A-8F7B-71803CA66EDF@microsoft.com...
> >I tried this formula and I can not seem to make it work. Is the MAX IF
> > formula supposed to generate a zero as a result?
> >
> >
> >
> > "Aladin Akyurek" wrote:
> >
> >> D2: 3/1/05
> >> E2: 3/31/05
> >> F2:
> >>
> >> =MAX(IF(($A$1:$A$4>=D2)*($A$1:$A$4<=E2),$A$1:$A$4))
> >>
> >> which must be confirmed with control+shift+enter instead of just with
> >> enter.
> >>
> >> G2:
> >>
> >> =SUMIF($A$1:$A$4,F2,$B$1:$B$4)
> >>
> >> SleazyBreezy wrote:
> >> > Okay, now I have a second, similar problem. Here's my new table:
> >> >
> >> > A1: 03/01/2005
> >> > B1: $5.00
> >> > A2: 03/05/2005
> >> > B2: $12.00
> >> > A3: 04/05/2005
> >> > B3: $3.00
> >> > A4: 03/05/2005
> >> > B4: $50.00
> >> >
> >> > As before, the A column is named Base_Date and the B column is named
> >> > Base_Amount. I want to modify the SUMIF formula, as used below, so that
> >> > it
> >> > adds only the records associated with the *latest* date in a specified
> >> > date
> >> > range. Referencing the table above, if I specify >03/01/2005 and
> >> > <=03/31/2005, the formula should add B2+B4, and not include B1.
> >> > However, if I
> >> > then add the following to the table:
> >> >
> >> > A5: 03/29/2005
> >> > B5: $1.00
> >> >
> >> > The formula should only report back B5 for its value, as A5 is the
> >> > latest
> >> > date in the allowed range.
> >> >
> >> > Thanks again in advance.
> >> >
> >> > :-)
> >> >
> >> > - Sleazy
> >> >
> >> > "SleazyBreezy" wrote:
> >> >
> >> >
> >> >>Thank you very much! Thankfully, my date criteria is static, so the
> >> >>X2/Y2
> >> >>variables are not needed. I used your formula to achieve the desired
> >> >>result
> >> >>by moving the closing quotes as follows:
> >> >>
> >> >>=SUMIF(Base_Date,">=03/01/2005",Base_Amount)-SUMIF(Base_Date,">03/31/2005",Base_Amount)
> >>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
The SUMIF function can work with one criteria given. How can I modify a
formula by adding other functions to make the SUMIF work with TWO criterion
met?
Thanks
"SleazyBreezy" wrote:
> Thank you very much! Thankfully, my date criteria is static, so the X2/Y2
> variables are not needed. I used your formula to achieve the desired result
> by moving the closing quotes as follows:
>
> =SUMIF(Base_Date,">=03/01/2005",Base_Amount)-SUMIF(Base_Date,">03/31/2005",Base_Amount)
>
>
> "Aladin Akyurek" wrote:
>
> > =SUMIF(Base_Date,">="&X2,Base_Amount)-SUMIF(Base_Date,">"&Y2,Base_Amount)
> >
> > where X2 houses the first day date of the month/year of interset like:
> > 03/01/2005 and Y2 one of:
> >
> > 03/31/2005
> >
> > =DATE(YEAR(X2),MONTH(X2)+1,0)
> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Hi
Up until XL2003, Sumif takes a single Criterion.
With XL2007, SUMIFS allows multiple criteria.
If you are using 2003 or lower, consider the use of Sumproduct.
=SUMPRODUCT((Range1=X1)*(Range2=Y1)*(Range3=Z1)*Range4)
Where Range 4 is the values to be Summed, the other Ranges are what you are
wanting to match against certain values held in cells X1, Y1 and Z1.
Ensure the Ranges are of equal size, and in 2003 and lower, ranges cannot be
whole columns.
--
Regards
Roger Govier
"tywlam" <tywlam@discussions.microsoft.com> wrote in message
news:85CA43C7-7119-4CD8-8C18-E9546C297DD4@microsoft.com...
> The SUMIF function can work with one criteria given. How can I modify a
> formula by adding other functions to make the SUMIF work with TWO
> criterion
> met?
>
> Thanks
>
>
>
> "SleazyBreezy" wrote:
>
>> Thank you very much! Thankfully, my date criteria is static, so the X2/Y2
>> variables are not needed. I used your formula to achieve the desired
>> result
>> by moving the closing quotes as follows:
>>
>> =SUMIF(Base_Date,">=03/01/2005",Base_Amount)-SUMIF(Base_Date,">03/31/2005",Base_Amount)
>>
>>
>> "Aladin Akyurek" wrote:
>>
>> > =SUMIF(Base_Date,">="&X2,Base_Amount)-SUMIF(Base_Date,">"&Y2,Base_Amount)
>> >
>> > where X2 houses the first day date of the month/year of interset like:
>> > 03/01/2005 and Y2 one of:
>> >
>> > 03/31/2005
>> >
>> > =DATE(YEAR(X2),MONTH(X2)+1,0)
>> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Thanks Roger. But it just couldn't work! Let me take an example below:
Col.A Col.B Col.C
Row1 Project Date Amount
Row2 AAA 01/03/2008 200
Row3 BBB 01/03/2008 100
Row4 AAA 15/03/2008 20
Row5 CCC 02/03/3008 10
Row6 DDD 01/04/2008 200
Row7 CCC 27/03/2008 130
Row8 AAA 05/04/2008 30
I'd like to sum the values in Col.C with project name of AAA in Col.A and
date of March 2008 in Col.B. I write the the formula:
=SUMPRODUCT((A1:A8="AAA")*(B1:B8="**/03/****")*(C1:C8)) but it gives the
result of zero! The correct answer should be 220. (200+20)
Please help!
Regards,
Terry
"Roger Govier" wrote:
> Hi
>
> Up until XL2003, Sumif takes a single Criterion.
> With XL2007, SUMIFS allows multiple criteria.
>
> If you are using 2003 or lower, consider the use of Sumproduct.
>
> =SUMPRODUCT((Range1=X1)*(Range2=Y1)*(Range3=Z1)*Range4)
> Where Range 4 is the values to be Summed, the other Ranges are what you are
> wanting to match against certain values held in cells X1, Y1 and Z1.
> Ensure the Ranges are of equal size, and in 2003 and lower, ranges cannot be
> whole columns.
>
> --
> Regards
> Roger Govier
>
> "tywlam" <tywlam@discussions.microsoft.com> wrote in message
> news:85CA43C7-7119-4CD8-8C18-E9546C297DD4@microsoft.com...
> > The SUMIF function can work with one criteria given. How can I modify a
> > formula by adding other functions to make the SUMIF work with TWO
> > criterion
> > met?
> >
> > Thanks
> >
> >
> >
> > "SleazyBreezy" wrote:
> >
> >> Thank you very much! Thankfully, my date criteria is static, so the X2/Y2
> >> variables are not needed. I used your formula to achieve the desired
> >> result
> >> by moving the closing quotes as follows:
> >>
> >> =SUMIF(Base_Date,">=03/01/2005",Base_Amount)-SUMIF(Base_Date,">03/31/2005",Base_Amount)
> >>
> >>
> >> "Aladin Akyurek" wrote:
> >>
> >> > =SUMIF(Base_Date,">="&X2,Base_Amount)-SUMIF(Base_Date,">"&Y2,Base_Amount)
> >> >
> >> > where X2 houses the first day date of the month/year of interset like:
> >> > 03/01/2005 and Y2 one of:
> >> >
> >> > 03/31/2005
> >> >
> >> > =DATE(YEAR(X2),MONTH(X2)+1,0)
> >> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Hi
It will work, if you need to change the formula slightly.
dates are stored as serial numbers, hence they will never be "seen" as **
/03/ ****.
You need to compare the Text(date,"mmyyyy") to get your result.
=SUMPRODUCT((A1:A8="AAA")*(TEXT(B1:B8,"mmyyyy")="032008")*C1:C8)
You don't need parentheses around the C values, as they are your data and
are not being used in any comparison.
Including them, will nit prevent the formula working - they are just not
necessary.
--
Regards
Roger Govier
"tywlam" <tywlam@discussions.microsoft.com> wrote in message
news:6A526E8A-C57A-435A-98ED-111290BF43B4@microsoft.com...
> Thanks Roger. But it just couldn't work! Let me take an example below:
>
> Col.A Col.B Col.C
> Row1 Project Date Amount
> Row2 AAA 01/03/2008 200
> Row3 BBB 01/03/2008 100
> Row4 AAA 15/03/2008 20
> Row5 CCC 02/03/3008 10
> Row6 DDD 01/04/2008 200
> Row7 CCC 27/03/2008 130
> Row8 AAA 05/04/2008 30
>
> I'd like to sum the values in Col.C with project name of AAA in Col.A and
> date of March 2008 in Col.B. I write the the formula:
> =SUMPRODUCT((A1:A8="AAA")*(B1:B8="**/03/****")*(C1:C8)) but it gives the
> result of zero! The correct answer should be 220. (200+20)
>
> Please help!
>
> Regards,
> Terry
>
>
> "Roger Govier" wrote:
>
>> Hi
>>
>> Up until XL2003, Sumif takes a single Criterion.
>> With XL2007, SUMIFS allows multiple criteria.
>>
>> If you are using 2003 or lower, consider the use of Sumproduct.
>>
>> =SUMPRODUCT((Range1=X1)*(Range2=Y1)*(Range3=Z1)*Range4)
>> Where Range 4 is the values to be Summed, the other Ranges are what you
>> are
>> wanting to match against certain values held in cells X1, Y1 and Z1.
>> Ensure the Ranges are of equal size, and in 2003 and lower, ranges cannot
>> be
>> whole columns.
>>
>> --
>> Regards
>> Roger Govier
>>
>> "tywlam" <tywlam@discussions.microsoft.com> wrote in message
>> news:85CA43C7-7119-4CD8-8C18-E9546C297DD4@microsoft.com...
>> > The SUMIF function can work with one criteria given. How can I modify
>> > a
>> > formula by adding other functions to make the SUMIF work with TWO
>> > criterion
>> > met?
>> >
>> > Thanks
>> >
>> >
>> >
>> > "SleazyBreezy" wrote:
>> >
>> >> Thank you very much! Thankfully, my date criteria is static, so the
>> >> X2/Y2
>> >> variables are not needed. I used your formula to achieve the desired
>> >> result
>> >> by moving the closing quotes as follows:
>> >>
>> >> =SUMIF(Base_Date,">=03/01/2005",Base_Amount)-SUMIF(Base_Date,">03/31/2005",Base_Amount)
>> >>
>> >>
>> >> "Aladin Akyurek" wrote:
>> >>
>> >> > =SUMIF(Base_Date,">="&X2,Base_Amount)-SUMIF(Base_Date,">"&Y2,Base_Amount)
>> >> >
>> >> > where X2 houses the first day date of the month/year of interset
>> >> > like:
>> >> > 03/01/2005 and Y2 one of:
>> >> >
>> >> > 03/31/2005
>> >> >
>> >> > =DATE(YEAR(X2),MONTH(X2)+1,0)
>> >> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
It works when both criteria and formula are in the same worksheet. Thank
you. But when the formula is not in the same worksheet, it give all zero
results!! I've checked the formula carefully and also used the functions bar
dialog box to set up my formula. It still doesn't work! Do you know why?
Regards,
"Roger Govier" wrote:
> Hi
>
> It will work, if you need to change the formula slightly.
> dates are stored as serial numbers, hence they will never be "seen" as **
> /03/ ****.
> You need to compare the Text(date,"mmyyyy") to get your result.
>
> =SUMPRODUCT((A1:A8="AAA")*(TEXT(B1:B8,"mmyyyy")="032008")*C1:C8)
>
> You don't need parentheses around the C values, as they are your data and
> are not being used in any comparison.
> Including them, will nit prevent the formula working - they are just not
> necessary.
>
> --
> Regards
> Roger Govier
>
> "tywlam" <tywlam@discussions.microsoft.com> wrote in message
> news:6A526E8A-C57A-435A-98ED-111290BF43B4@microsoft.com...
> > Thanks Roger. But it just couldn't work! Let me take an example below:
> >
> > Col.A Col.B Col.C
> > Row1 Project Date Amount
> > Row2 AAA 01/03/2008 200
> > Row3 BBB 01/03/2008 100
> > Row4 AAA 15/03/2008 20
> > Row5 CCC 02/03/3008 10
> > Row6 DDD 01/04/2008 200
> > Row7 CCC 27/03/2008 130
> > Row8 AAA 05/04/2008 30
> >
> > I'd like to sum the values in Col.C with project name of AAA in Col.A and
> > date of March 2008 in Col.B. I write the the formula:
> > =SUMPRODUCT((A1:A8="AAA")*(B1:B8="**/03/****")*(C1:C8)) but it gives the
> > result of zero! The correct answer should be 220. (200+20)
> >
> > Please help!
> >
> > Regards,
> > Terry
> >
> >
> > "Roger Govier" wrote:
> >
> >> Hi
> >>
> >> Up until XL2003, Sumif takes a single Criterion.
> >> With XL2007, SUMIFS allows multiple criteria.
> >>
> >> If you are using 2003 or lower, consider the use of Sumproduct.
> >>
> >> =SUMPRODUCT((Range1=X1)*(Range2=Y1)*(Range3=Z1)*Range4)
> >> Where Range 4 is the values to be Summed, the other Ranges are what you
> >> are
> >> wanting to match against certain values held in cells X1, Y1 and Z1.
> >> Ensure the Ranges are of equal size, and in 2003 and lower, ranges cannot
> >> be
> >> whole columns.
> >>
> >> --
> >> Regards
> >> Roger Govier
> >>
> >> "tywlam" <tywlam@discussions.microsoft.com> wrote in message
> >> news:85CA43C7-7119-4CD8-8C18-E9546C297DD4@microsoft.com...
> >> > The SUMIF function can work with one criteria given. How can I modify
> >> > a
> >> > formula by adding other functions to make the SUMIF work with TWO
> >> > criterion
> >> > met?
> >> >
> >> > Thanks
> >> >
> >> >
> >> >
> >> > "SleazyBreezy" wrote:
> >> >
> >> >> Thank you very much! Thankfully, my date criteria is static, so the
> >> >> X2/Y2
> >> >> variables are not needed. I used your formula to achieve the desired
> >> >> result
> >> >> by moving the closing quotes as follows:
> >> >>
> >> >> =SUMIF(Base_Date,">=03/01/2005",Base_Amount)-SUMIF(Base_Date,">03/31/2005",Base_Amount)
> >> >>
> >> >>
> >> >> "Aladin Akyurek" wrote:
> >> >>
> >> >> > =SUMIF(Base_Date,">="&X2,Base_Amount)-SUMIF(Base_Date,">"&Y2,Base_Amount)
> >> >> >
> >> >> > where X2 houses the first day date of the month/year of interset
> >> >> > like:
> >> >> > 03/01/2005 and Y2 one of:
> >> >> >
> >> >> > 03/31/2005
> >> >> >
> >> >> > =DATE(YEAR(X2),MONTH(X2)+1,0)
> >> >> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
Question
Notify me of replies