Something Missing
I have a problem with the following formula
=SUMIF(Sheet1!$F$3:$F$248,"<"&DATE(2007,1,1),Sheet 1!$M$3:$M$248)/SUMIF(Sheet1!$F$3:$F$248,"<"&DATE(2007,1,1),Sheet1 !$D$3:$D$248) Based on the SUMIF statement I look at a date range and if it falls within a certain time frame I take the sum of a dollar column and divide it by the total cost of another column to get a %. This works as is, but what I need this do is now is roughly the same thing except I do not want the $0 values to column M to be calculated in the overall formula. I believe the $0 value is throwing my outputted % number off. Does someone have a better way? Thanks Peter W |
Something Missing
If you had this data in column M and D
M D 1 59 2 48 0 26 4 55 what would be your desired result? |
Something Missing
With M being the dollars made and D being the dollar sold a third range would
column F (Date range) I want to find all the dates within a range (F2:F1000) and sum all the dollars made within that range (M2:M1000) and divide the sum against the total dollars sold for that range. If any number in column M is 0 I don't want to add the corresponding cell in column D to the total, this throws my total percentage off because the 0 is being included in the overall performance. "Herbert Seidenberg" wrote: If you had this data in column M and D M D 1 59 2 48 0 26 4 55 what would be your desired result? |
Something Missing
For this example, with the defined names as shown
DaT DoM DoS 12/28/2006 1 59 12/29/2006 2 48 12/30/2006 0 26 12/31/2006 4 55 1/1/2007 5 23 1/2/2007 6 61 =SUMPRODUCT((DaT<DATE(2007,1,1))*DoM)/ SUMPRODUCT((DaT<DATE(2007,1,1))*(DoM0)*DoS) =4.32% Your SUMIF formula would give the wrong answer of 3.72% |
Something Missing
Thanks Herbert, this worked great.
Peter "Herbert Seidenberg" wrote: For this example, with the defined names as shown DaT DoM DoS 12/28/2006 1 59 12/29/2006 2 48 12/30/2006 0 26 12/31/2006 4 55 1/1/2007 5 23 1/2/2007 6 61 =SUMPRODUCT((DaT<DATE(2007,1,1))*DoM)/ SUMPRODUCT((DaT<DATE(2007,1,1))*(DoM0)*DoS) =4.32% Your SUMIF formula would give the wrong answer of 3.72% |
All times are GMT +1. The time now is 06:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com