Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Amy via OfficeKB.com
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Amy via OfficeKB.com
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Countif or Sumproduct Harley Excel Discussion (Misc queries) 8 December 22nd 05 01:34 AM
SumProduct or CountIf Kim Excel Worksheet Functions 7 July 9th 05 12:04 AM
countif, sumproduct mg New Users to Excel 7 July 1st 05 10:26 PM
Using COUNTIF with 2 criteria - SUMPRODUCT? Mike R. Excel Worksheet Functions 2 February 24th 05 06:57 AM
SUMPRODUCT & COUNTIF Connie Martin Excel Worksheet Functions 2 December 16th 04 07:53 PM


All times are GMT +1. The time now is 08:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"