SUMIF help needed

[Walter]

I am trying to sum daily entries on one worksheet to a weekly total on a
summary worksheet. I have named the following ranges:
summary date column = Summary_Date
brakes date column = Brakes_Date
brakes debit column = Brakes_Debit_Column
brakes credit column = Brakes_Credit_Column

Here is the formula I have but it is returning 0.
=SUMIF(Brakes_Date,AND(Brakes_Date<=Summary!$A9,Brakes_Date>Summary!$A8),Brakes_Credit_Column)
--
Thanks for your help,
Walter

[JulieD]

Hi Walter

a SUMIF can only take one criteria, try
=SUMPRODUCT(--(Brakes_Date<=Summary!$A9),--(Brakes_Date>Summary!$A8),Brakes_Credit_Column)

ensuring that all your ranges have the same dimensions.
check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
for more details on this function

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Walter" <Walter@discussions.microsoft.com> wrote in message
news:0ECDFBCC-54AC-41B3-8F0E-C332A7BEE574@microsoft.com...
>I am trying to sum daily entries on one worksheet to a weekly total on a
> summary worksheet. I have named the following ranges:
> summary date column = Summary_Date
> brakes date column = Brakes_Date
> brakes debit column = Brakes_Debit_Column
> brakes credit column = Brakes_Credit_Column
>
> Here is the formula I have but it is returning 0.
> =SUMIF(Brakes_Date,AND(Brakes_Date<=Summary!$A9,Brakes_Date>Summary!$A8),Brakes_Credit_Column)
> --
> Thanks for your help,
> Walter

[Walter]

Thanks Julie for your help. It worked although I'm not quite sure why since
its supposed to multiply the cells. But there's a lot I don't understand.
Many thanks again.
--
Thanks for your help,
Walter


"JulieD" wrote:

> Hi Walter
>
> a SUMIF can only take one criteria, try
> =SUMPRODUCT(--(Brakes_Date<=Summary!$A9),--(Brakes_Date>Summary!$A8),Brakes_Credit_Column)
>
> ensuring that all your ranges have the same dimensions.
> check out
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> for more details on this function
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "Walter" <Walter@discussions.microsoft.com> wrote in message
> news:0ECDFBCC-54AC-41B3-8F0E-C332A7BEE574@microsoft.com...
> >I am trying to sum daily entries on one worksheet to a weekly total on a
> > summary worksheet. I have named the following ranges:
> > summary date column = Summary_Date
> > brakes date column = Brakes_Date
> > brakes debit column = Brakes_Debit_Column
> > brakes credit column = Brakes_Credit_Column
> >
> > Here is the formula I have but it is returning 0.
> > =SUMIF(Brakes_Date,AND(Brakes_Date<=Summary!$A9,Brakes_Date>Summary!$A8),Brakes_Credit_Column)
> > --
> > Thanks for your help,
> > Walter
>
>
>

[Walter]

I am trying to nest SUMPRODUCT inside SUM to total 3 worksheets into 1
summary sheet. When I do this, I get an incorrect total. I tried it in
stages and found the problem to be in the service section. The brakes and
tires return and total the correct figure. When I add the service, the total
increases by 209.22 when the correct amount is 51.68. I can't see any
difference in the formula and I don't see a relationship between the numbers.
Here is my formula:
=SUM(SUMPRODUCT((Brakes_Date<=$A3)*(Brakes_Date>$A2)*(Brakes_Debit)),SUMPRODUCT((Service_Date<=$A3)*(Service_Date>$A2)*(Service_Debit)),SUMPRODUCT((Tires_Date<=$A3)*(Tires_Date>$A2)*(Tires_Debit)))
BTW:
Brakes_Debit = 25.84
Service_Debit = 51.68
Tires_Debit = 25.84
Formula result = 260.90
--
Thanks for your help,
Walter


"JulieD" wrote:

> Hi Walter
>
> a SUMIF can only take one criteria, try
> =SUMPRODUCT(--(Brakes_Date<=Summary!$A9),--(Brakes_Date>Summary!$A8),Brakes_Credit_Column)
>
> ensuring that all your ranges have the same dimensions.
> check out
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> for more details on this function
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "Walter" <Walter@discussions.microsoft.com> wrote in message
> news:0ECDFBCC-54AC-41B3-8F0E-C332A7BEE574@microsoft.com...
> >I am trying to sum daily entries on one worksheet to a weekly total on a
> > summary worksheet. I have named the following ranges:
> > summary date column = Summary_Date
> > brakes date column = Brakes_Date
> > brakes debit column = Brakes_Debit_Column
> > brakes credit column = Brakes_Credit_Column
> >
> > Here is the formula I have but it is returning 0.
> > =SUMIF(Brakes_Date,AND(Brakes_Date<=Summary!$A9,Brakes_Date>Summary!$A8),Brakes_Credit_Column)
> > --
> > Thanks for your help,
> > Walter
>
>
>

[Walter]

Nevermind...Someone else looked over this with me & saw that we had a date
problem on service worksheet. Once I got that corrected, everything added up
right. So, the formulas were working fine all along.
--
Thanks for your help,
Walter


"Walter" wrote:

> I am trying to nest SUMPRODUCT inside SUM to total 3 worksheets into 1
> summary sheet. When I do this, I get an incorrect total. I tried it in
> stages and found the problem to be in the service section. The brakes and
> tires return and total the correct figure. When I add the service, the total
> increases by 209.22 when the correct amount is 51.68. I can't see any
> difference in the formula and I don't see a relationship between the numbers.
> Here is my formula:
> =SUM(SUMPRODUCT((Brakes_Date<=$A3)*(Brakes_Date>$A2)*(Brakes_Debit)),SUMPRODUCT((Service_Date<=$A3)*(Service_Date>$A2)*(Service_Debit)),SUMPRODUCT((Tires_Date<=$A3)*(Tires_Date>$A2)*(Tires_Debit)))
> BTW:
> Brakes_Debit = 25.84
> Service_Debit = 51.68
> Tires_Debit = 25.84
> Formula result = 260.90
> --
> Thanks for your help,
> Walter
>
>
> "JulieD" wrote:
>
> > Hi Walter
> >
> > a SUMIF can only take one criteria, try
> > =SUMPRODUCT(--(Brakes_Date<=Summary!$A9),--(Brakes_Date>Summary!$A8),Brakes_Credit_Column)
> >
> > ensuring that all your ranges have the same dimensions.
> > check out
> > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> > for more details on this function
> >
> > --
> > Cheers
> > JulieD
> > check out www.hcts.net.au/tipsandtricks.htm
> > ....well i'm working on it anyway
> > "Walter" <Walter@discussions.microsoft.com> wrote in message
> > news:0ECDFBCC-54AC-41B3-8F0E-C332A7BEE574@microsoft.com...
> > >I am trying to sum daily entries on one worksheet to a weekly total on a
> > > summary worksheet. I have named the following ranges:
> > > summary date column = Summary_Date
> > > brakes date column = Brakes_Date
> > > brakes debit column = Brakes_Debit_Column
> > > brakes credit column = Brakes_Credit_Column
> > >
> > > Here is the formula I have but it is returning 0.
> > > =SUMIF(Brakes_Date,AND(Brakes_Date<=Summary!$A9,Brakes_Date>Summary!$A8),Brakes_Credit_Column)
> > > --
> > > Thanks for your help,
> > > Walter
> >
> >
> >

[JulieD]

glad its solved


--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Walter" <Walter@discussions.microsoft.com> wrote in message
news:5AE8ED54-0453-4FEC-9AB0-B35FF18D4E62@microsoft.com...
> Nevermind...Someone else looked over this with me & saw that we had a date
> problem on service worksheet. Once I got that corrected, everything added
> up
> right. So, the formulas were working fine all along.
> --
> Thanks for your help,
> Walter
>
>
> "Walter" wrote:
>
>> I am trying to nest SUMPRODUCT inside SUM to total 3 worksheets into 1
>> summary sheet. When I do this, I get an incorrect total. I tried it in
>> stages and found the problem to be in the service section. The brakes
>> and
>> tires return and total the correct figure. When I add the service, the
>> total
>> increases by 209.22 when the correct amount is 51.68. I can't see any
>> difference in the formula and I don't see a relationship between the
>> numbers.
>> Here is my formula:
>> =SUM(SUMPRODUCT((Brakes_Date<=$A3)*(Brakes_Date>$A2)*(Brakes_Debit)),SUMPRODUCT((Service_Date<=$A3)*(Service_Date>$A2)*(Service_Debit)),SUMPRODUCT((Tires_Date<=$A3)*(Tires_Date>$A2)*(Tires_Debit)))
>> BTW:
>> Brakes_Debit = 25.84
>> Service_Debit = 51.68
>> Tires_Debit = 25.84
>> Formula result = 260.90
>> --
>> Thanks for your help,
>> Walter
>>
>>
>> "JulieD" wrote:
>>
>> > Hi Walter
>> >
>> > a SUMIF can only take one criteria, try
>> > =SUMPRODUCT(--(Brakes_Date<=Summary!$A9),--(Brakes_Date>Summary!$A8),Brakes_Credit_Column)
>> >
>> > ensuring that all your ranges have the same dimensions.
>> > check out
>> > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>> > for more details on this function
>> >
>> > --
>> > Cheers
>> > JulieD
>> > check out www.hcts.net.au/tipsandtricks.htm
>> > ....well i'm working on it anyway
>> > "Walter" <Walter@discussions.microsoft.com> wrote in message
>> > news:0ECDFBCC-54AC-41B3-8F0E-C332A7BEE574@microsoft.com...
>> > >I am trying to sum daily entries on one worksheet to a weekly total on
>> > >a
>> > > summary worksheet. I have named the following ranges:
>> > > summary date column = Summary_Date
>> > > brakes date column = Brakes_Date
>> > > brakes debit column = Brakes_Debit_Column
>> > > brakes credit column = Brakes_Credit_Column
>> > >
>> > > Here is the formula I have but it is returning 0.
>> > > =SUMIF(Brakes_Date,AND(Brakes_Date<=Summary!$A9,Brakes_Date>Summary!$A8),Brakes_Credit_Column)
>> > > --
>> > > Thanks for your help,
>> > > Walter
>> >
>> >
>> >

Hi

Try using SUMPRODUCT()
==SUMPRODUCT((Brakes_Date<=Summary!$A9)*(Brakes_Date>Summary!$A8)*(Brakes_Credit_Column))
If you want to use SUMIF, you'll have to do it in two steps. You'll need to
total the values above the first date and then subtract the total of the
values above the second date.

--
Andy.


"Walter" <Walter@discussions.microsoft.com> wrote in message
news:0ECDFBCC-54AC-41B3-8F0E-C332A7BEE574@microsoft.com...
>I am trying to sum daily entries on one worksheet to a weekly total on a
> summary worksheet. I have named the following ranges:
> summary date column = Summary_Date
> brakes date column = Brakes_Date
> brakes debit column = Brakes_Debit_Column
> brakes credit column = Brakes_Credit_Column
>
> Here is the formula I have but it is returning 0.
> =SUMIF(Brakes_Date,AND(Brakes_Date<=Summary!$A9,Brakes_Date>Summary!$A8),Brakes_Credit_Column)
> --
> Thanks for your help,
> Walter

[Walter]

Thanks Andy. I would have never thought of trying a function that is
supposed to multiply the cells but it works.
--
Thanks for your help,
Walter


"Andy B" wrote:

> Hi
>
> Try using SUMPRODUCT()
> ==SUMPRODUCT((Brakes_Date<=Summary!$A9)*(Brakes_Date>Summary!$A8)*(Brakes_Credit_Column))
> If you want to use SUMIF, you'll have to do it in two steps. You'll need to
> total the values above the first date and then subtract the total of the
> values above the second date.
>
> --
> Andy.
>
>
> "Walter" <Walter@discussions.microsoft.com> wrote in message
> news:0ECDFBCC-54AC-41B3-8F0E-C332A7BEE574@microsoft.com...
> >I am trying to sum daily entries on one worksheet to a weekly total on a
> > summary worksheet. I have named the following ranges:
> > summary date column = Summary_Date
> > brakes date column = Brakes_Date
> > brakes debit column = Brakes_Debit_Column
> > brakes credit column = Brakes_Credit_Column
> >
> > Here is the formula I have but it is returning 0.
> > =SUMIF(Brakes_Date,AND(Brakes_Date<=Summary!$A9,Brakes_Date>Summary!$A8),Brakes_Credit_Column)
> > --
> > Thanks for your help,
> > Walter
>
>
>

Well, the way it works is that each part of the SUMPRODUCT formula will
return a 1 (if it's true) or a 0 (if it's false). SUMPRODUCT will then
multiply these together to get the result. For more info, have a look here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Andy.


"Walter" <Walter@discussions.microsoft.com> wrote in message
news:67447DBE-5D84-41B6-AB22-12363E8CF4FD@microsoft.com...
> Thanks Andy. I would have never thought of trying a function that is
> supposed to multiply the cells but it works.
> --
> Thanks for your help,
> Walter
>
>
> "Andy B" wrote:
>
>> Hi
>>
>> Try using SUMPRODUCT()
>> ==SUMPRODUCT((Brakes_Date<=Summary!$A9)*(Brakes_Date>Summary!$A8)*(Brakes_Credit_Column))
>> If you want to use SUMIF, you'll have to do it in two steps. You'll need
>> to
>> total the values above the first date and then subtract the total of the
>> values above the second date.
>>
>> --
>> Andy.
>>
>>
>> "Walter" <Walter@discussions.microsoft.com> wrote in message
>> news:0ECDFBCC-54AC-41B3-8F0E-C332A7BEE574@microsoft.com...
>> >I am trying to sum daily entries on one worksheet to a weekly total on a
>> > summary worksheet. I have named the following ranges:
>> > summary date column = Summary_Date
>> > brakes date column = Brakes_Date
>> > brakes debit column = Brakes_Debit_Column
>> > brakes credit column = Brakes_Credit_Column
>> >
>> > Here is the formula I have but it is returning 0.
>> > =SUMIF(Brakes_Date,AND(Brakes_Date<=Summary!$A9,Brakes_Date>Summary!$A8),Brakes_Credit_Column)
>> > --
>> > Thanks for your help,
>> > Walter
>>
>>
>>

[Walter]

OK. I think I have an idea now.
Appreciate your help.
--
Thanks for your help,
Walter


"Andy B" wrote:

> Well, the way it works is that each part of the SUMPRODUCT formula will
> return a 1 (if it's true) or a 0 (if it's false). SUMPRODUCT will then
> multiply these together to get the result. For more info, have a look here:
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> --
> Andy.
>
>
> "Walter" <Walter@discussions.microsoft.com> wrote in message
> news:67447DBE-5D84-41B6-AB22-12363E8CF4FD@microsoft.com...
> > Thanks Andy. I would have never thought of trying a function that is
> > supposed to multiply the cells but it works.
> > --
> > Thanks for your help,
> > Walter
> >
> >
> > "Andy B" wrote:
> >
> >> Hi
> >>
> >> Try using SUMPRODUCT()
> >> ==SUMPRODUCT((Brakes_Date<=Summary!$A9)*(Brakes_Date>Summary!$A8)*(Brakes_Credit_Column))
> >> If you want to use SUMIF, you'll have to do it in two steps. You'll need
> >> to
> >> total the values above the first date and then subtract the total of the
> >> values above the second date.
> >>
> >> --
> >> Andy.
> >>
> >>
> >> "Walter" <Walter@discussions.microsoft.com> wrote in message
> >> news:0ECDFBCC-54AC-41B3-8F0E-C332A7BEE574@microsoft.com...
> >> >I am trying to sum daily entries on one worksheet to a weekly total on a
> >> > summary worksheet. I have named the following ranges:
> >> > summary date column = Summary_Date
> >> > brakes date column = Brakes_Date
> >> > brakes debit column = Brakes_Debit_Column
> >> > brakes credit column = Brakes_Credit_Column
> >> >
> >> > Here is the formula I have but it is returning 0.
> >> > =SUMIF(Brakes_Date,AND(Brakes_Date<=Summary!$A9,Brakes_Date>Summary!$A8),Brakes_Credit_Column)
> >> > --
> >> > Thanks for your help,
> >> > Walter
> >>
> >>
> >>
>
>
>