Something Missing

[Looping through]

I have a problem with the following formula

=SUMIF(Sheet1!$F$3:$F$248,"<"&DATE(2007,1,1),Sheet1!$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

 

[Herbert Seidenberg]

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?

 

[Looping through]

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]

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))*(DoM>0)*DoS)
=4.32%
Your SUMIF formula would give the wrong answer of 3.72%

 

[Looping through]

Thanks Herbert, this worked great.

Peter