Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Question
I am currently using a very cumbersome calculation to return a value in Cells
H3:H100 that is this: =SUM(IF(Data!$A$2:$A$2500=DATEVALUE("1/1/2005"),IF(Data!$A$2:$A$2500<=DATEVALUE("2/1/2005"),IF(Data!$D$2:$D$2500<=1,Data!$F$2:$F$2500,0 ),0),0)) I built this formula using the conditional sum function in MS Excel. I unfortunately have to use this type of formula over 500 times on this particular sheet, and was hoping someone might be able to show me a better way to get this value through using the sumproduct function. Thank you! Gina |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Question
I should also mention that I do have the start-end dates in the A (start date) and B (end date) column in the same row as the calculation (column I) on this worksheet. That means I could use those cells as reference instead of DateValue if it would be easier? Thanks, Gina |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Question
=SUMPRODUCT(--(Data!$A$2:$A$2500=date_ref1),--(Data!$A$2:$A$2500<=date_ref2),--(Data!$D$2:$D$2500<=1),Data!$F$2:$F$2500)
If I understand correctly, sum the values of F, based on 3 criteria? I think that will work. date_ref1 and 2 would be your dates. -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Gina" wrote: I am currently using a very cumbersome calculation to return a value in Cells H3:H100 that is this: =SUM(IF(Data!$A$2:$A$2500=DATEVALUE("1/1/2005"),IF(Data!$A$2:$A$2500<=DATEVALUE("2/1/2005"),IF(Data!$D$2:$D$2500<=1,Data!$F$2:$F$2500,0 ),0),0)) I built this formula using the conditional sum function in MS Excel. I unfortunately have to use this type of formula over 500 times on this particular sheet, and was hoping someone might be able to show me a better way to get this value through using the sumproduct function. Thank you! Gina |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Question
"M Kan" wrote: =SUMPRODUCT(--(Data!$A$2:$A$2500=date_ref1),--(Data!$A$2:$A$2500<=date_ref2),--(Data!$D$2:$D$2500<=1),Data!$F$2:$F$2500) If I understand correctly, sum the values of F, based on 3 criteria? I think that will work. date_ref1 and 2 would be your dates. -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Gina" wrote: I am currently using a very cumbersome calculation to return a value in Cells H3:H100 that is this: =SUM(IF(Data!$A$2:$A$2500=DATEVALUE("1/1/2005"),IF(Data!$A$2:$A$2500<=DATEVALUE("2/1/2005"),IF(Data!$D$2:$D$2500<=1,Data!$F$2:$F$2500,0 ),0),0)) I built this formula using the conditional sum function in MS Excel. I unfortunately have to use this type of formula over 500 times on this particular sheet, and was hoping someone might be able to show me a better way to get this value through using the sumproduct function. Thank you! Gina This was perfect. I put in the date references and it returned exactly the value that I needed. Thank you so much. Gina |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Question
Thanks for the feedback, glad I could help out.
-- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Gina" wrote: "M Kan" wrote: =SUMPRODUCT(--(Data!$A$2:$A$2500=date_ref1),--(Data!$A$2:$A$2500<=date_ref2),--(Data!$D$2:$D$2500<=1),Data!$F$2:$F$2500) If I understand correctly, sum the values of F, based on 3 criteria? I think that will work. date_ref1 and 2 would be your dates. -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Gina" wrote: I am currently using a very cumbersome calculation to return a value in Cells H3:H100 that is this: =SUM(IF(Data!$A$2:$A$2500=DATEVALUE("1/1/2005"),IF(Data!$A$2:$A$2500<=DATEVALUE("2/1/2005"),IF(Data!$D$2:$D$2500<=1,Data!$F$2:$F$2500,0 ),0),0)) I built this formula using the conditional sum function in MS Excel. I unfortunately have to use this type of formula over 500 times on this particular sheet, and was hoping someone might be able to show me a better way to get this value through using the sumproduct function. Thank you! Gina This was perfect. I put in the date references and it returned exactly the value that I needed. Thank you so much. Gina |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct question | Excel Discussion (Misc queries) | |||
Sumproduct question | Excel Discussion (Misc queries) | |||
Sumproduct Question | Excel Worksheet Functions | |||
SUMPRODUCT question | Excel Worksheet Functions | |||
SUMPRODUCT question | Excel Worksheet Functions |