| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count if 0 and between dates
I have been looking all over this site trying to find the answer and came up
with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if 0 and between dates
On Tue, 16 Dec 2008 10:15:02 -0800, Stilmovin
wrote: I have been looking all over this site trying to find the answer and came up with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! Try this formula: =SUMPRODUCT((E5:E80=startdate)*(E5:E80<=enddate)* (R5:R80)) where startdate and enddate are two cells with those dates, i.e. A1 and A2 The formula gives the sum of award for the corresponding dates. If you just want to know the number of dates where there has been an award, change R:R80 to R5:R80<"" in the last part of the formula. Hope this helps / Lars-Åke |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if 0 and between dates
=SUMPRODUCT((E5:E80=2008/5/1)*(E5:E80<=2008/5/31)*(P5:P80))
turned result = 0 Result should be = 3 "Lars-Ã…ke Aspelin" wrote: On Tue, 16 Dec 2008 10:15:02 -0800, Stilmovin wrote: I have been looking all over this site trying to find the answer and came up with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! Try this formula: =SUMPRODUCT((E5:E80=startdate)*(E5:E80<=enddate)* (R5:R80)) where startdate and enddate are two cells with those dates, i.e. A1 and A2 The formula gives the sum of award for the corresponding dates. If you just want to know the number of dates where there has been an award, change R:R80 to R5:R80<"" in the last part of the formula. Hope this helps / Lars-Ã…ke |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if 0 and between dates
=SUMIF(E5:E80,"=10/9/2008",J5:J80)-SUMIF(E5:E80,"=10/10/2008"",J5:J80)
assuming award numbers in column J. -- __________________________________ HTH Bob "Stilmovin" wrote in message ... I have been looking all over this site trying to find the answer and came up with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if 0 and between dates
Use cells to hold your date boundaries:
A1 = start date = 1/1/2008 B1 = end date = 1/31/2008 A3:A12 = dates If you're using Excel 2007: =COUNTIFS(A3:A12,"="&A1,A3:A12,"<="&B1) For any version of Excel: =COUNTIF(A3:A12,"="&A1)-COUNTIF(A3:A12,""&B1) -- Biff Microsoft Excel MVP "Stilmovin" wrote in message ... I have been looking all over this site trying to find the answer and came up with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if 0 and between dates
This equation returns the total of that range. What do i need to include if i
just want them to be counted. "Bob Phillips" wrote: =SUMIF(E5:E80,"=10/9/2008",J5:J80)-SUMIF(E5:E80,"=10/10/2008"",J5:J80) assuming award numbers in column J. -- __________________________________ HTH Bob "Stilmovin" wrote in message ... I have been looking all over this site trying to find the answer and came up with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if 0 and between dates
That is because the first portion of your formula evaluates to E5:E80=401.6
(2008 divided by 5 divided by 1) and the second is E5:E80<=12.9548 (2008 divided by 5 divided by 31). There are no numbers greater than 401.6 and less than 12.9548, therefore the answer will always be 0. If you want the correct answer, use a cell to hold the date or use DATE(year, month, day) in your formula. Stilmovin wrote: =SUMPRODUCT((E5:E80=2008/5/1)*(E5:E80<=2008/5/31)*(P5:P80)) turned result = 0 Result should be = 3 "Lars-Ã…ke Aspelin" wrote: On Tue, 16 Dec 2008 10:15:02 -0800, Stilmovin wrote: I have been looking all over this site trying to find the answer and came up with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! Try this formula: =SUMPRODUCT((E5:E80=startdate)*(E5:E80<=enddate)* (R5:R80)) where startdate and enddate are two cells with those dates, i.e. A1 and A2 The formula gives the sum of award for the corresponding dates. If you just want to know the number of dates where there has been an award, change R:R80 to R5:R80<"" in the last part of the formula. Hope this helps / Lars-Ã…ke |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if 0 and between dates
Read my answer again, particularly the line below the formula.
Lars-Åke On Tue, 16 Dec 2008 10:39:01 -0800, Stilmovin wrote: =SUMPRODUCT((E5:E80=2008/5/1)*(E5:E80<=2008/5/31)*(P5:P80)) turned result = 0 Result should be = 3 "Lars-Åke Aspelin" wrote: On Tue, 16 Dec 2008 10:15:02 -0800, Stilmovin wrote: I have been looking all over this site trying to find the answer and came up with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! Try this formula: =SUMPRODUCT((E5:E80=startdate)*(E5:E80<=enddate)* (R5:R80)) where startdate and enddate are two cells with those dates, i.e. A1 and A2 The formula gives the sum of award for the corresponding dates. If you just want to know the number of dates where there has been an award, change R:R80 to R5:R80<"" in the last part of the formula. Hope this helps / Lars-Åke |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if 0 and between dates
Alright i used your formula to come up with a result and it would just sum. I
need it to count the number of awarded. not total the awarded. "Lars-Ã…ke Aspelin" wrote: Read my answer again, particularly the line below the formula. Lars-Ã…ke On Tue, 16 Dec 2008 10:39:01 -0800, Stilmovin wrote: =SUMPRODUCT((E5:E80=2008/5/1)*(E5:E80<=2008/5/31)*(P5:P80)) turned result = 0 Result should be = 3 "Lars-Ã…ke Aspelin" wrote: On Tue, 16 Dec 2008 10:15:02 -0800, Stilmovin wrote: I have been looking all over this site trying to find the answer and came up with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! Try this formula: =SUMPRODUCT((E5:E80=startdate)*(E5:E80<=enddate)* (R5:R80)) where startdate and enddate are two cells with those dates, i.e. A1 and A2 The formula gives the sum of award for the corresponding dates. If you just want to know the number of dates where there has been an award, change R:R80 to R5:R80<"" in the last part of the formula. Hope this helps / Lars-Ã…ke |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if 0 and between dates
And for that you have to read the last line of my answer (where there
is a 5 missing) Lars-Åke On Tue, 16 Dec 2008 11:44:08 -0800, Stilmovin wrote: Alright i used your formula to come up with a result and it would just sum. I need it to count the number of awarded. not total the awarded. "Lars-Åke Aspelin" wrote: Read my answer again, particularly the line below the formula. Lars-Åke On Tue, 16 Dec 2008 10:39:01 -0800, Stilmovin wrote: =SUMPRODUCT((E5:E80=2008/5/1)*(E5:E80<=2008/5/31)*(P5:P80)) turned result = 0 Result should be = 3 "Lars-Åke Aspelin" wrote: On Tue, 16 Dec 2008 10:15:02 -0800, Stilmovin wrote: I have been looking all over this site trying to find the answer and came up with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! Try this formula: =SUMPRODUCT((E5:E80=startdate)*(E5:E80<=enddate)* (R5:R80)) where startdate and enddate are two cells with those dates, i.e. A1 and A2 The formula gives the sum of award for the corresponding dates. If you just want to know the number of dates where there has been an award, change R:R80 to R5:R80<"" in the last part of the formula. Hope this helps / Lars-Åke |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if 0 and between dates
Alright sorry i forgot the "" behind them. That is why mine wasn't working.
Is there a way to get the equation to not relate to a specific cell but Date(2008,5,1) "Lars-Ã…ke Aspelin" wrote: And for that you have to read the last line of my answer (where there is a 5 missing) Lars-Ã…ke On Tue, 16 Dec 2008 11:44:08 -0800, Stilmovin wrote: Alright i used your formula to come up with a result and it would just sum. I need it to count the number of awarded. not total the awarded. "Lars-Ã…ke Aspelin" wrote: Read my answer again, particularly the line below the formula. Lars-Ã…ke On Tue, 16 Dec 2008 10:39:01 -0800, Stilmovin wrote: =SUMPRODUCT((E5:E80=2008/5/1)*(E5:E80<=2008/5/31)*(P5:P80)) turned result = 0 Result should be = 3 "Lars-Ã…ke Aspelin" wrote: On Tue, 16 Dec 2008 10:15:02 -0800, Stilmovin wrote: I have been looking all over this site trying to find the answer and came up with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! Try this formula: =SUMPRODUCT((E5:E80=startdate)*(E5:E80<=enddate)* (R5:R80)) where startdate and enddate are two cells with those dates, i.e. A1 and A2 The formula gives the sum of award for the corresponding dates. If you just want to know the number of dates where there has been an award, change R:R80 to R5:R80<"" in the last part of the formula. Hope this helps / Lars-Ã…ke |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if 0 and between dates
Yes, you may use the DATE function instead of a reference to a cell
holding the date, as pointed out by Glenn earlier in this thread. Lars-Åke On Tue, 16 Dec 2008 13:46:09 -0800, Stilmovin wrote: Alright sorry i forgot the "" behind them. That is why mine wasn't working. Is there a way to get the equation to not relate to a specific cell but Date(2008,5,1) "Lars-Åke Aspelin" wrote: And for that you have to read the last line of my answer (where there is a 5 missing) Lars-Åke On Tue, 16 Dec 2008 11:44:08 -0800, Stilmovin wrote: Alright i used your formula to come up with a result and it would just sum. I need it to count the number of awarded. not total the awarded. "Lars-Åke Aspelin" wrote: Read my answer again, particularly the line below the formula. Lars-Åke On Tue, 16 Dec 2008 10:39:01 -0800, Stilmovin wrote: =SUMPRODUCT((E5:E80=2008/5/1)*(E5:E80<=2008/5/31)*(P5:P80)) turned result = 0 Result should be = 3 "Lars-Åke Aspelin" wrote: On Tue, 16 Dec 2008 10:15:02 -0800, Stilmovin wrote: I have been looking all over this site trying to find the answer and came up with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! Try this formula: =SUMPRODUCT((E5:E80=startdate)*(E5:E80<=enddate)* (R5:R80)) where startdate and enddate are two cells with those dates, i.e. A1 and A2 The formula gives the sum of award for the corresponding dates. If you just want to know the number of dates where there has been an award, change R:R80 to R5:R80<"" in the last part of the formula. Hope this helps / Lars-Åke |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if 0 and between dates
=SUMPRODUCT((E6:E80=DATE(2008,8,1)*(E6:E80<=DATE( 2008,8,30)*(P6:P80<""))))
why is this not returning the results i want. It is not counting up the cells. i tryed changing the range and using different dates. but it keeps turning up "0"? "Lars-Ã…ke Aspelin" wrote: Yes, you may use the DATE function instead of a reference to a cell holding the date, as pointed out by Glenn earlier in this thread. Lars-Ã…ke On Tue, 16 Dec 2008 13:46:09 -0800, Stilmovin wrote: Alright sorry i forgot the "" behind them. That is why mine wasn't working. Is there a way to get the equation to not relate to a specific cell but Date(2008,5,1) "Lars-Ã…ke Aspelin" wrote: And for that you have to read the last line of my answer (where there is a 5 missing) Lars-Ã…ke On Tue, 16 Dec 2008 11:44:08 -0800, Stilmovin wrote: Alright i used your formula to come up with a result and it would just sum. I need it to count the number of awarded. not total the awarded. "Lars-Ã…ke Aspelin" wrote: Read my answer again, particularly the line below the formula. Lars-Ã…ke On Tue, 16 Dec 2008 10:39:01 -0800, Stilmovin wrote: =SUMPRODUCT((E5:E80=2008/5/1)*(E5:E80<=2008/5/31)*(P5:P80)) turned result = 0 Result should be = 3 "Lars-Ã…ke Aspelin" wrote: On Tue, 16 Dec 2008 10:15:02 -0800, Stilmovin wrote: I have been looking all over this site trying to find the answer and came up with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! Try this formula: =SUMPRODUCT((E5:E80=startdate)*(E5:E80<=enddate)* (R5:R80)) where startdate and enddate are two cells with those dates, i.e. A1 and A2 The formula gives the sum of award for the corresponding dates. If you just want to know the number of dates where there has been an award, change R:R80 to R5:R80<"" in the last part of the formula. Hope this helps / Lars-Ã…ke |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if 0 and between dates
The reason is that you misplaced two right parenthesis.
This is how it should look like: =SUMPRODUCT((E6:E80=DATE(2008,8,1))*(E6:E80<=DATE (2008,8,30))*(P6:P80<"")) Below I put in some linefeeds and spaces so that you can see easier how the formula is built up. =SUMPRODUCT( ( E6:E80=DATE(2008,8,1) ) * ( E6:E80<=DATE(2008,8,30) ) * ( P6:P80<"" ) ) Hope this helps / Lars-Åke On Wed, 17 Dec 2008 07:47:14 -0800, Stilmovin wrote: =SUMPRODUCT((E6:E80=DATE(2008,8,1)*(E6:E80<=DATE (2008,8,30)*(P6:P80<"")))) why is this not returning the results i want. It is not counting up the cells. i tryed changing the range and using different dates. but it keeps turning up "0"? "Lars-Åke Aspelin" wrote: Yes, you may use the DATE function instead of a reference to a cell holding the date, as pointed out by Glenn earlier in this thread. Lars-Åke On Tue, 16 Dec 2008 13:46:09 -0800, Stilmovin wrote: Alright sorry i forgot the "" behind them. That is why mine wasn't working. Is there a way to get the equation to not relate to a specific cell but Date(2008,5,1) "Lars-Åke Aspelin" wrote: And for that you have to read the last line of my answer (where there is a 5 missing) Lars-Åke On Tue, 16 Dec 2008 11:44:08 -0800, Stilmovin wrote: Alright i used your formula to come up with a result and it would just sum. I need it to count the number of awarded. not total the awarded. "Lars-Åke Aspelin" wrote: Read my answer again, particularly the line below the formula. Lars-Åke On Tue, 16 Dec 2008 10:39:01 -0800, Stilmovin wrote: =SUMPRODUCT((E5:E80=2008/5/1)*(E5:E80<=2008/5/31)*(P5:P80)) turned result = 0 Result should be = 3 "Lars-Åke Aspelin" wrote: On Tue, 16 Dec 2008 10:15:02 -0800, Stilmovin wrote: I have been looking all over this site trying to find the answer and came up with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! Try this formula: =SUMPRODUCT((E5:E80=startdate)*(E5:E80<=enddate)* (R5:R80)) where startdate and enddate are two cells with those dates, i.e. A1 and A2 The formula gives the sum of award for the corresponding dates. If you just want to know the number of dates where there has been an award, change R:R80 to R5:R80<"" in the last part of the formula. Hope this helps / Lars-Åke |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if 0 and between dates
But isn't there a number of those awarded with each date, so you sum the
award numbers? -- __________________________________ HTH Bob "Stilmovin" wrote in message ... This equation returns the total of that range. What do i need to include if i just want them to be counted. "Bob Phillips" wrote: =SUMIF(E5:E80,"=10/9/2008",J5:J80)-SUMIF(E5:E80,"=10/10/2008"",J5:J80) assuming award numbers in column J. -- __________________________________ HTH Bob "Stilmovin" wrote in message ... I have been looking all over this site trying to find the answer and came up with nothing. I have a spread sheet that contains three columns: Date Cost Award I am trying to count the number of awarded between a date(January) I have tried the sumproduct and sumif equations to come up with the following =COUNTIFS(P6:R93,"0",E6:E93,"2008Jan") - #Value! =COUNTIF(E5:E80,"=10/9/2008")-COUNTIF(E5:E80,"=10/10/2008") - This resulted in only counting the dates and not the # of Awarded HELP!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count on dates | Excel Worksheet Functions | |||
| Count if Dates are! | Excel Discussion (Misc queries) | |||
| I want to count the total Number of dates between two dates How? | Excel Worksheet Functions | |||
| Count Dates? | Excel Worksheet Functions | |||
| need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
Linear Mode
