SUMIF help needed
 

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,Br akes_DateSummary!$A8),Brakes_Credit_Column)
--
Thanks for your help,
Walter

Hi Walter

a SUMIF can only take one criteria, try
=SUMPRODUCT(--(Brakes_Date<=Summary!$A9),--(Brakes_DateSummary!$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" wrote in message
...
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,Br akes_DateSummary!$A8),Brakes_Credit_Column)
--
Thanks for your help,
Walter

Hi

Try using SUMPRODUCT()
==SUMPRODUCT((Brakes_Date<=Summary!$A9)*(Brakes_Da teSummary!$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" wrote in message
...
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,Br akes_DateSummary!$A8),Brakes_Credit_Column)
--
Thanks for your help,
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_DateSummary!$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" wrote in message
...
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,Br akes_DateSummary!$A8),Brakes_Credit_Column)
--
Thanks for your help,
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_Da teSummary!$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" wrote in message
...
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,Br akes_DateSummary!$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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Andy.


"Walter" wrote in message
...
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_Da teSummary!$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" wrote in message
...
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,Br akes_DateSummary!$A8),Brakes_Credit_Column)
--
Thanks for your help,
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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Andy.


"Walter" wrote in message
...
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_Da teSummary!$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" wrote in message
...
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,Br akes_DateSummary!$A8),Brakes_Credit_Column)
--
Thanks for your help,
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$A 2)*(Brakes_Debit)),SUMPRODUCT((Service_Date<=$A3)* (Service_Date$A2)*(Service_Debit)),SUMPRODUCT((Ti res_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_DateSummary!$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" wrote in message
...
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,Br akes_DateSummary!$A8),Brakes_Credit_Column)
--
Thanks for your help,
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$A 2)*(Brakes_Debit)),SUMPRODUCT((Service_Date<=$A3)* (Service_Date$A2)*(Service_Debit)),SUMPRODUCT((Ti res_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_DateSummary!$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" wrote in message
...
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,Br akes_DateSummary!$A8),Brakes_Credit_Column)
--
Thanks for your help,
Walter

glad its solved


--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Walter" wrote in message
...
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$A 2)*(Brakes_Debit)),SUMPRODUCT((Service_Date<=$A3)* (Service_Date$A2)*(Service_Debit)),SUMPRODUCT((Ti res_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_DateSummary!$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" wrote in message
...
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,Br akes_DateSummary!$A8),Brakes_Credit_Column)
--
Thanks for your help,
Walter