Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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??? |
#2
|
|||
|
|||
=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??? |
#3
|
|||
|
|||
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??? |
#4
|
|||
|
|||
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??? |
#5
|
|||
|
|||
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??? |
#6
|
|||
|
|||
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??? |
#7
|
|||
|
|||
"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. |
#8
|
|||
|
|||
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??? |
#9
|
|||
|
|||
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??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
SUMIF Not | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
help with "criterea" in the sumif function | Excel Worksheet Functions | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions |