Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
SUMIF Not | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
help with "criterea" in the sumif function | Excel Worksheet Functions | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions |