SUMIF help needed

W

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)
 
G

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.
 
W

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 said:
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
Click to expand...
 
W

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 said:
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.
Click to expand...
 
W

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 said:
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
Click to expand...
 
W

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 said:
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
Click to expand...
 
Top