Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT or COUNTIF?
I need to get a few counts of data I have entered in several columns filled
with dates from 2004 through 2006 (and will be soon be populated with more dates from 2006). My goal is to get accurate counts of the dates (by month), so I can populate a chart. The problem I am running into, is that if I use the formula below (where Column J has corresponding numbers of the months): =SUMPRODUCT(--(ISNUMBER(Sheet1!D6:D28)),--(MONTH(Sheet1!D6:D28)=J6)) It's picking up the data for all years - this is a problem. For January, I want to count all dates in Jan of 2006 and any dates prior to Jan of 2006. For Feb, I want to count all dates in Feb 2006 only, and then the same for March through Dec of 2006. I guess I need two different formulas: (1) One to count all dates in Jan of 2006 and before; and (2) One to count all dates in each month for 2006. I've been searching the discussions in this section, but have not found anything that might help me. Any assistance is GREATLY appreciated! -- Thank you, Amy@H-D Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT or COUNTIF?
I think I'd add a category:
Before 2006: =SUMPRODUCT(--(ISNUMBER(Sheet1!D6:D28)),--(year(Sheet1!D6:D28)<2006)) Then use this for all the other months: =SUMPRODUCT(--(ISNUMBER(Sheet1!D6:D28)),--(MONTH(Sheet1!D6:D28)=J6), --year(sheet1!d6:d28=2006)) Or you could just use a giant formula in the January cell that adds both. "Amy via OfficeKB.com" wrote: I need to get a few counts of data I have entered in several columns filled with dates from 2004 through 2006 (and will be soon be populated with more dates from 2006). My goal is to get accurate counts of the dates (by month), so I can populate a chart. The problem I am running into, is that if I use the formula below (where Column J has corresponding numbers of the months): =SUMPRODUCT(--(ISNUMBER(Sheet1!D6:D28)),--(MONTH(Sheet1!D6:D28)=J6)) It's picking up the data for all years - this is a problem. For January, I want to count all dates in Jan of 2006 and any dates prior to Jan of 2006. For Feb, I want to count all dates in Feb 2006 only, and then the same for March through Dec of 2006. I guess I need two different formulas: (1) One to count all dates in Jan of 2006 and before; and (2) One to count all dates in each month for 2006. I've been searching the discussions in this section, but have not found anything that might help me. Any assistance is GREATLY appreciated! -- Thank you, Amy@H-D Message posted via http://www.officekb.com -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT or COUNTIF?
That worked! Thank you so much, both Gary and Dave. I combined both of your
suggestions and it was successful. Gary L Brown wrote: Not tried but won't the following do? =SUMPRODUCT(--(ISNUMBER(Sheet1!D6:D28)),--(MONTH(Sheet1!D6:D28)=J6),--(YEAR(Sheet1!D6:D28)=2006)) I need to get a few counts of data I have entered in several columns filled with dates from 2004 through 2006 (and will be soon be populated with more [quoted text clipped - 17 lines] Any assistance is GREATLY appreciated! -- Thank you, Amy@H-D Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200601/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif or Sumproduct | Excel Discussion (Misc queries) | |||
SumProduct or CountIf | Excel Worksheet Functions | |||
countif, sumproduct | New Users to Excel | |||
Using COUNTIF with 2 criteria - SUMPRODUCT? | Excel Worksheet Functions | |||
SUMPRODUCT & COUNTIF | Excel Worksheet Functions |