COUNTIFS AND CRITERIA
 

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(R150,(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?

COUNTIFS AND CRITERIA
 

Use

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

should get you started


---
HTH

Bob Phillips

"Eagle amongst turkeys" <Eagle amongst
wrote in message ...
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(R150,(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?

COUNTIFS AND CRITERIA
 

Hi,

Try this

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

R1012 has the date for which you want to count.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eagle amongst turkeys" <Eagle amongst
wrote in message ...
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(R150,(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?

COUNTIFS AND CRITERIA
 

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)*(... ?
--
David Biddulph

"Ashish Mathur" wrote in message
...
Hi,

Try this

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

R1012 has the date for which you want to count.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eagle amongst turkeys" <Eagle amongst
wrote in message
...
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(R150,(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?