SUMIF help needed

[Guest]

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)

 

[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

 

[Guest]

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.

 

[Guest]

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

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

 

[Guest]

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

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.

 

[Guest]

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.

Thanks Andy. I would have never thought of trying a function that is
supposed to multiply the cells but it works.

 

[Guest]

OK. I think I have an idea now.
Appreciate your help.

 

[Guest]

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

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

 

[Guest]

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

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

 

[JulieD]

glad its solved