SumIf Help??
 

I have the following formula set-up to calculate a certain cell range based
on the date range. I also would like to put in one criteria, but I can't
seem to get it to work.

Help?

=SUMIF(O2:O200,"=04/01/2005",R2:R200)-SUMIF(O2:O200,"04/07/05",R2:R200)

Example - If the date range falls between these dates and is coded "pp/nc".
How do I add in the other criteria???

=SUMPRODUCT(--(O2:O200=--"2005-04-01"),--(O2:O200<"2005-04-07"),--(P2:P200=
"pp/nc"),R2:R200)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Maddie" wrote in message
...
I have the following formula set-up to calculate a certain cell range
based
on the date range. I also would like to put in one criteria, but I can't
seem to get it to work.

Help?

=SUMIF(O2:O200,"=04/01/2005",R2:R200)-SUMIF(O2:O200,"04/07/05",R2:R200)

Example - If the date range falls between these dates and is coded
"pp/nc".
How do I add in the other criteria???

one possibility would be to use Sumproduct as in
=sumproduct(--(O2:O200=04/01/2005),(R2:R200),--(O2:O20004/07/05),--(range
="pp/nc") )
You may have to use the seriallized dates, dates do strange things in my
computer.

"Maddie" wrote:

I have the following formula set-up to calculate a certain cell range based
on the date range. I also would like to put in one criteria, but I can't
seem to get it to work.

Help?

=SUMIF(O2:O200,"=04/01/2005",R2:R200)-SUMIF(O2:O200,"04/07/05",R2:R200)

Example - If the date range falls between these dates and is coded "pp/nc".
How do I add in the other criteria???

What do the -- represent???? Can I still use the quotes with a number.

Example - Instead of "pp/nc" I want it to bring back those that have a
number 4 in the column??

"Bob Phillips" wrote:

=SUMPRODUCT(--(O2:O200=--"2005-04-01"),--(O2:O200<"2005-04-07"),--(P2:P200=
"pp/nc"),R2:R200)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Maddie" wrote in message
...
I have the following formula set-up to calculate a certain cell range
based
on the date range. I also would like to put in one criteria, but I can't
seem to get it to work.

Help?

=SUMIF(O2:O200,"=04/01/2005",R2:R200)-SUMIF(O2:O200,"04/07/05",R2:R200)

Example - If the date range falls between these dates and is coded
"pp/nc".
How do I add in the other criteria???

from your question to bob

(O2:O200=04/01/2005) when used this way is a conditional function with a
"true" / "false" response. putting the -- in front of it converts it to
being a 1,0 response. any conditionally equation cna be used in this manner

(note i did have to convert ot the date number when i checked it this time

=sumproduct(--(O2:O200=38433),(R2:R200),--(O2:O20038449),--(code range =4)
would be a valid equation.
"bj" wrote:

one possibility would be to use Sumproduct as in
=sumproduct(--(O2:O200=04/01/2005),(R2:R200),--(O2:O20004/07/05),--(range
="pp/nc") )
You may have to use the seriallized dates, dates do strange things in my
computer.

"Maddie" wrote:

I have the following formula set-up to calculate a certain cell range based
on the date range. I also would like to put in one criteria, but I can't
seem to get it to work.

Help?

=SUMIF(O2:O200,"=04/01/2005",R2:R200)-SUMIF(O2:O200,"04/07/05",R2:R200)

Example - If the date range falls between these dates and is coded "pp/nc".
How do I add in the other criteria???

I can't seem to get it to work. The following formula brings back exactly
what I need, but it doesn't have the date range. Can you show me how to just
add this or will sumproduct be the best:

=sum(if(O2:O200=I7,IF(P2:P200=I6,IF(N2:N200=DATEV ALUE("4/15/2005"),1,0))))

N2:N200 = DATES
I6 = YES
I7 = 4
The above counts each cell with the date after 04-15, but when I do another
week it keeps adding to this. I need it between 04-15-05 and 04-21-05??
"bj" wrote:

one possibility would be to use Sumproduct as in
=sumproduct(--(O2:O200=04/01/2005),(R2:R200),--(O2:O20004/07/05),--(range
="pp/nc") )
You may have to use the seriallized dates, dates do strange things in my
computer.

"Maddie" wrote:

I have the following formula set-up to calculate a certain cell range based
on the date range. I also would like to put in one criteria, but I can't
seem to get it to work.

Help?

=SUMIF(O2:O200,"=04/01/2005",R2:R200)-SUMIF(O2:O200,"04/07/05",R2:R200)

Example - If the date range falls between these dates and is coded "pp/nc".
How do I add in the other criteria???

"Maddie" wrote in message
...
What do the -- represent????

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for an explanation.

Can I still use the quotes with a number.
Example - Instead of "pp/nc" I want it to bring back those that have a
number 4 in the column??

The quotes signify text, so if testing for a number, remove the quotes,
express the number without them.

try
=sum(if(O2:O200=I7,IF(P2:P200=I6,IF(N2:N200=DATEV ALUE("4/15/2005"),1,0)))*(if(O2:O200=I7,IF(P2:P200=I6,IF(N2:N 200<DATEVALUE("4/21/2005"),1,0))))
entered as an array

"Maddie" wrote:

I can't seem to get it to work. The following formula brings back exactly
what I need, but it doesn't have the date range. Can you show me how to just
add this or will sumproduct be the best:

=sum(if(O2:O200=I7,IF(P2:P200=I6,IF(N2:N200=DATEV ALUE("4/15/2005"),1,0))))

N2:N200 = DATES
I6 = YES
I7 = 4
The above counts each cell with the date after 04-15, but when I do another
week it keeps adding to this. I need it between 04-15-05 and 04-21-05??
"bj" wrote:

one possibility would be to use Sumproduct as in
=sumproduct(--(O2:O200=04/01/2005),(R2:R200),--(O2:O20004/07/05),--(range
="pp/nc") )
You may have to use the seriallized dates, dates do strange things in my
computer.

"Maddie" wrote:

I have the following formula set-up to calculate a certain cell range based
on the date range. I also would like to put in one criteria, but I can't
seem to get it to work.

Help?

=SUMIF(O2:O200,"=04/01/2005",R2:R200)-SUMIF(O2:O200,"04/07/05",R2:R200)

Example - If the date range falls between these dates and is coded "pp/nc".
How do I add in the other criteria???

Thank you so much. It worked. I almost had it. I appreciate you sticking
with me on this. :)

"bj" wrote:

try
=sum(if(O2:O200=I7,IF(P2:P200=I6,IF(N2:N200=DATEV ALUE("4/15/2005"),1,0)))*(if(O2:O200=I7,IF(P2:P200=I6,IF(N2:N 200<DATEVALUE("4/21/2005"),1,0))))
entered as an array

"Maddie" wrote:

I can't seem to get it to work. The following formula brings back exactly
what I need, but it doesn't have the date range. Can you show me how to just
add this or will sumproduct be the best:

=sum(if(O2:O200=I7,IF(P2:P200=I6,IF(N2:N200=DATEV ALUE("4/15/2005"),1,0))))

N2:N200 = DATES
I6 = YES
I7 = 4
The above counts each cell with the date after 04-15, but when I do another
week it keeps adding to this. I need it between 04-15-05 and 04-21-05??
"bj" wrote:

one possibility would be to use Sumproduct as in
=sumproduct(--(O2:O200=04/01/2005),(R2:R200),--(O2:O20004/07/05),--(range
="pp/nc") )
You may have to use the seriallized dates, dates do strange things in my
computer.

"Maddie" wrote:

I have the following formula set-up to calculate a certain cell range based
on the date range. I also would like to put in one criteria, but I can't
seem to get it to work.

Help?

=SUMIF(O2:O200,"=04/01/2005",R2:R200)-SUMIF(O2:O200,"04/07/05",R2:R200)

Example - If the date range falls between these dates and is coded "pp/nc".
How do I add in the other criteria???