count a value in a date range based on a date in another workshee.

[marsjune68]

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.

 

[T. Valko]

Try this:

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

Copy down as needed.

 

[marsjune68]

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.

 

[T. Valko]

=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.

 

[marsjune68]

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...