COUNTIFS AND CRITERIA

[Eagle amongst turkeys]

I have a spreadsheet that is counting information based on a certain date..
however many loads to deliver for x date. the part i'm stuck on is telling
excel that i want to know how many loads for a specific date pick up in
within a zip code range and deliver to another zip code range. i'm working
with 1 week's worth of data at a time. i have the formula to count the loads
for each day ...=COUNTIFS(J5:J500,"<12/12/2009")... i have the formula down
for one day's worth of info, but when there are more than 1 days worth, the
formula doesn't hold water.
=IF(R15>0,(COUNTIF(F5:F1010,">59999")-COUNTIF(F5:F1010,">61900")),0) R
equals the number of loads for that day... so it's saying if there is a load
that day, run the formula to count the zips. problem comes the following day
because it doesn't differentiate between the dates too. so i need it to say,
of the number of loads in field "r", how many are from within this zip code
range?

 

[Bob Phillips]

Use

SUMPRODUCT(--(F5:F1010>59999),--(F5:F1010<=61900),--(J5:J500=--"2009-12-12"))

should get you started

 

[Ashish Mathur]

Hi,

Try this

=sumproduct(($R$5:$R$1010>=$R$1012)*($R$5:$R$1010<=$R$1012)*(1*(F5:F1010)>59999)*((1*(F5:F1010)<61900))

R1012 has the date for which you want to count.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[David Biddulph]

Wouldn't
=sumproduct(($R$5:$R$1010>=$R$1012)*($R$5:$R$1010<=$R$1012)*(...
be the same as
=sumproduct(($R$5:$R$1010=$R$1012)*(... ?