Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > count a value in a date range based on a date in another workshee.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 16
Default count a value in a date range based on a date in another workshee.
I want to count the number of "x" in a given date range in sheet2. sheet1 has
every date listed from Jan1 2009 thru Dec 31 2009 I want to see daily how
many "X" I have. The total should post in to col B of sheet1.


Sheet 1 has dates in col A col B is where I want the result from the cout
of "X" to go.

date total
col A col B
row1: 1/1/09 3
row2: 1/2/09 2
row3: 1/3/09 1
row4: 1/4/09

SHEET 2
has multiple entries for each day


date "X" or "Y"
col A col Z
row1: 1/1/09 x
row2: 1/1/09 y
row3: 1/1/09 x
row4: 1/1/09 x
row5: 1/2/09 y
row6: 1/2/09 x
row7: 1/2/09 x
row8: 1/3/09 y
row9: 1/3/09 x
row10: 1/3/09 y

I am killing myself trying to figure it out but I can not. I hope it is a
easy one. But I can not figure it out. If someone could help me I would be
happy.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15,768
Default count a value in a date range based on a date in another workshee.
Try this:

=SUMPRODUCT(--(Sheet2!A$1:A$10=A1),--(Sheet2!Z$1:Z$10="x"))

Copy down as needed.

--
Biff
Microsoft Excel MVP


"marsjune68" wrote in message
...
I want to count the number of "x" in a given date range in sheet2. sheet1
has
every date listed from Jan1 2009 thru Dec 31 2009 I want to see daily
how
many "X" I have. The total should post in to col B of sheet1.


Sheet 1 has dates in col A col B is where I want the result from the
cout
of "X" to go.

date total
col A col B
row1: 1/1/09 3
row2: 1/2/09 2
row3: 1/3/09 1
row4: 1/4/09

SHEET 2
has multiple entries for each day


date "X" or "Y"
col A col Z
row1: 1/1/09 x
row2: 1/1/09 y
row3: 1/1/09 x
row4: 1/1/09 x
row5: 1/2/09 y
row6: 1/2/09 x
row7: 1/2/09 x
row8: 1/3/09 y
row9: 1/3/09 x
row10: 1/3/09 y

I am killing myself trying to figure it out but I can not. I hope it is a
easy one. But I can not figure it out. If someone could help me I would be
happy.


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 16
Default count a value in a date range based on a date in another workshee.
Thank you that worked! How about this one. I thought I could get it to work
but its not working very well. Here is the issue.
Sheet "Daily totals" Every Row has a date. I want to see daily what each
total was. I will be doing this for every day of the year. I can get my
totals for Col C and Col D to work. But they are a different formula.

date total total total
Col B Col C Col D Col E
Row 1 1/1/09 0 0 0
Row 2 1/2/09 7 5 5
Row 3 1/3/09 15 2 11
Row 4 1/4/09 9 5 9
Row 5 1/5/09 7 1 7
Row 6 1/6/09 1 0 1

Col E Formula is
=SUMPRODUCT(--(Jan!B3:B400='Daily totals'!B3),--(Jan!FO3:FO400<="1"))

Sheet "Jan"
Col B Col FO
1/2/09 2
1/2/09 0
1/2/09 0
1/2/09 1
1/2/09 1
1/2/09 2
1/2/09 0
1/3/09 1
1/3/09 0
1/3/09 4
1/3/09 0
1/3/09 0
1/3/09 1
1/3/09 0
1/3/09 0

"Col FO" formula is =COUNTIF(U3:FK3,"y")
I get accurate total from the formula =COUNTIF(U3:FK3,"y") this part works
right.

I can not get my Daily total in "Col E of sheet Daily Totals" to total
right. On the 1/2/09 will but 1/3/09 seems to count the 1/2 and 1/3 dates.
I want to know the total from "Sheet Jan" on a given day to total up in the
approprate date on the Daily totals sheet.

"marsjune68" wrote:

I want to count the number of "x" in a given date range in sheet2. sheet1 has
every date listed from Jan1 2009 thru Dec 31 2009 I want to see daily how
many "X" I have. The total should post in to col B of sheet1.


Sheet 1 has dates in col A col B is where I want the result from the cout
of "X" to go.

date total
col A col B
row1: 1/1/09 3
row2: 1/2/09 2
row3: 1/3/09 1
row4: 1/4/09

SHEET 2
has multiple entries for each day


date "X" or "Y"
col A col Z
row1: 1/1/09 x
row2: 1/1/09 y
row3: 1/1/09 x
row4: 1/1/09 x
row5: 1/2/09 y
row6: 1/2/09 x
row7: 1/2/09 x
row8: 1/3/09 y
row9: 1/3/09 x
row10: 1/3/09 y

I am killing myself trying to figure it out but I can not. I hope it is a
easy one. But I can not figure it out. If someone could help me I would be
happy.
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15,768
Default count a value in a date range based on a date in another workshee.
=SUMPRODUCT(--(Jan!B3:B400='Daily totals'!B3),--(Jan!FO3:FO400<="1"))

Remove the quotes from around the 1:

=SUMPRODUCT(--(Jan!B3:B400='Daily totals'!B3),--(Jan!FO3:FO400<=1))

When you quote numbers Excel then evaluates them as TEXT.

--
Biff
Microsoft Excel MVP


"marsjune68" wrote in message
...
Thank you that worked! How about this one. I thought I could get it to
work
but its not working very well. Here is the issue.
Sheet "Daily totals" Every Row has a date. I want to see daily what each
total was. I will be doing this for every day of the year. I can get my
totals for Col C and Col D to work. But they are a different formula.

date total total total
Col B Col C Col D Col E
Row 1 1/1/09 0 0 0
Row 2 1/2/09 7 5 5
Row 3 1/3/09 15 2 11
Row 4 1/4/09 9 5 9
Row 5 1/5/09 7 1 7
Row 6 1/6/09 1 0 1

Col E Formula is
=SUMPRODUCT(--(Jan!B3:B400='Daily totals'!B3),--(Jan!FO3:FO400<="1"))

Sheet "Jan"
Col B Col FO
1/2/09 2
1/2/09 0
1/2/09 0
1/2/09 1
1/2/09 1
1/2/09 2
1/2/09 0
1/3/09 1
1/3/09 0
1/3/09 4
1/3/09 0
1/3/09 0
1/3/09 1
1/3/09 0
1/3/09 0

"Col FO" formula is =COUNTIF(U3:FK3,"y")
I get accurate total from the formula =COUNTIF(U3:FK3,"y") this part works
right.

I can not get my Daily total in "Col E of sheet Daily Totals" to total
right. On the 1/2/09 will but 1/3/09 seems to count the 1/2 and 1/3
dates.
I want to know the total from "Sheet Jan" on a given day to total up in
the
approprate date on the Daily totals sheet.

"marsjune68" wrote:

I want to count the number of "x" in a given date range in sheet2. sheet1
has
every date listed from Jan1 2009 thru Dec 31 2009 I want to see daily
how
many "X" I have. The total should post in to col B of sheet1.


Sheet 1 has dates in col A col B is where I want the result from the
cout
of "X" to go.

date total
col A col B
row1: 1/1/09 3
row2: 1/2/09 2
row3: 1/3/09 1
row4: 1/4/09

SHEET 2
has multiple entries for each day


date "X" or "Y"
col A col Z
row1: 1/1/09 x
row2: 1/1/09 y
row3: 1/1/09 x
row4: 1/1/09 x
row5: 1/2/09 y
row6: 1/2/09 x
row7: 1/2/09 x
row8: 1/3/09 y
row9: 1/3/09 x
row10: 1/3/09 y

I am killing myself trying to figure it out but I can not. I hope it is a
easy one. But I can not figure it out. If someone could help me I would
be
happy.


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 16
Default count a value in a date range based on a date in another works
Thank you so much for your help. I am now a pain in the Arse I know. But I
am about to go jump off a bridge. Am I doing something wrong in there. Did I
miss something???


date tot1 tot2 tot3 tot4 this come from Sheet "Jan" Col FO
1/1/09 CLOSED 0 0 0 **should be 0 **
1/2/09 672 7 5 5 ** Should be 10 **
1/3/09 641 15 2 14 ** Should be 3 **
1/4/09 636 9 5 9 ** Should be 1 **

The formula for the col tot 4 is
=SUMPRODUCT(--(Jan!B3:B400='Daily totals'!B5),--(Jan!FO3:FO400<=1))
=SUMPRODUCT(--(Jan!B3:B400='Daily totals'!B6),--(Jan!FO3:FO400<=1))
=SUMPRODUCT(--(Jan!B3:B400='Daily totals'!B7),--(Jan!FO3:FO400<=1))
=SUMPRODUCT(--(Jan!B3:B400='Daily totals'!B8),--(Jan!FO3:FO400<=1))

B5 1/1/09
B6 1/2/09
B7 1/3/09
B8 1/4/09

Sheet "Jan"
Col B Col F
1 1/2/09 4
2 1/2/09 1
3 1/2/09 1
4 1/2/09 0
5 1/2/09 0
6 1/2/09 3
7 1/2/09 1 *** Actual tot for 1/2/09 10 ***
8 1/3/09 0
9 1/3/09 0
10 1/3/09 1
11 1/3/09 0
12 1/3/09 0
13 1/3/09 0
14 1/3/09 0
15 1/3/09 0
16 1/3/09 2
17 1/3/09 0
18 1/3/09 0
19 1/3/09 0
20 1/3/09 0
21 1/3/09 0
22 1/3/09 0 *** Actual tot for 1/3/09 3 ***
23 1/4/09 0
24 1/4/09 0
25 1/4/09 0
26 1/4/09 0
27 1/4/09 0
28 1/4/09 0
29 1/4/09 1
30 1/4/09 0
31 1/4/09 0 *** Actual tot for 1/4/09 1 ***

Here is what it is doing. This is the actual data.
Again Thank you so much...

"T. Valko" wrote:

=SUMPRODUCT(--(Jan!B3:B400='Daily totals'!B3),--(Jan!FO3:FO400<="1"))

Remove the quotes from around the 1:

=SUMPRODUCT(--(Jan!B3:B400='Daily totals'!B3),--(Jan!FO3:FO400<=1))

When you quote numbers Excel then evaluates them as TEXT.

--
Biff
Microsoft Excel MVP


"marsjune68" wrote in message
...
Thank you that worked! How about this one. I thought I could get it to
work
but its not working very well. Here is the issue.
Sheet "Daily totals" Every Row has a date. I want to see daily what each
total was. I will be doing this for every day of the year. I can get my
totals for Col C and Col D to work. But they are a different formula.

date total total total
Col B Col C Col D Col E
Row 1 1/1/09 0 0 0
Row 2 1/2/09 7 5 5
Row 3 1/3/09 15 2 11
Row 4 1/4/09 9 5 9
Row 5 1/5/09 7 1 7
Row 6 1/6/09 1 0 1

Col E Formula is
=SUMPRODUCT(--(Jan!B3:B400='Daily totals'!B3),--(Jan!FO3:FO400<="1"))

Sheet "Jan"
Col B Col FO
1/2/09 2
1/2/09 0
1/2/09 0
1/2/09 1
1/2/09 1
1/2/09 2
1/2/09 0
1/3/09 1
1/3/09 0
1/3/09 4
1/3/09 0
1/3/09 0
1/3/09 1
1/3/09 0
1/3/09 0

"Col FO" formula is =COUNTIF(U3:FK3,"y")
I get accurate total from the formula =COUNTIF(U3:FK3,"y") this part works
right.

I can not get my Daily total in "Col E of sheet Daily Totals" to total
right. On the 1/2/09 will but 1/3/09 seems to count the 1/2 and 1/3
dates.
I want to know the total from "Sheet Jan" on a given day to total up in
the
approprate date on the Daily totals sheet.

"marsjune68" wrote:

I want to count the number of "x" in a given date range in sheet2. sheet1
has
every date listed from Jan1 2009 thru Dec 31 2009 I want to see daily
how
many "X" I have. The total should post in to col B of sheet1.


Sheet 1 has dates in col A col B is where I want the result from the
cout
of "X" to go.

date total
col A col B
row1: 1/1/09 3
row2: 1/2/09 2
row3: 1/3/09 1
row4: 1/4/09

SHEET 2
has multiple entries for each day


date "X" or "Y"
col A col Z
row1: 1/1/09 x
row2: 1/1/09 y
row3: 1/1/09 x
row4: 1/1/09 x
row5: 1/2/09 y
row6: 1/2/09 x
row7: 1/2/09 x
row8: 1/3/09 y
row9: 1/3/09 x
row10: 1/3/09 y

I am killing myself trying to figure it out but I can not. I hope it is a
easy one. But I can not figure it out. If someone could help me I would
be
happy.



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
count based on 2 criteria (date and status) Rusty Excel Discussion (Misc queries) 4 April 4th 07 03:58 AM
How can I count a record if it contains a date within a date range hile trotman Excel Worksheet Functions 0 September 20th 06 08:58 PM
How to count dates within a certain range in a column with mutiple date range entries Krisjhn Excel Worksheet Functions 2 September 1st 05 01:59 PM
Count cells based on date range in another column [email protected] New Users to Excel 1 May 5th 05 08:11 PM
I have a list of dates that I need to count based on a date range ejb030353 Excel Worksheet Functions 4 November 24th 04 02:27 PM


All times are GMT +1. The time now is 09:04 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"

 

Contact Us ExcelBanter forum home Privacy Statement