Will not give accurate totals.

[marsjune68]

I can not get this to work right.

SHEET " Daily Totals"
col B
date total4 this come from Sheet "Jan" Col FO
1/1/09 0 **should be 0 **
1/2/09 5 ** Should be 10 **
1/3/09 14 ** Should be 3 **
1/4/09 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

Second Sheet

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

I am not getting accurate totals for each day. I want to total up the total
in "Sheet Jan" Col F based on the date and put the total in sheet " Daily
Totals" based on the date. I want to see every day that the totals are. I can
not get it to add up right. I have tried different thing but can not seem to
get it to work.

Again Thank you so much...

 

[Sheeloo]

Try
=SUMPRODUCT(--(Jan!B3:B400='Daily totals'!B5),(Jan!FO3:FO400))
Is it Col FO or F? If F then change FO to F above.
Why were you comparing FO with <=1? What you have is giving you the count in
Col FO with values less or equal to one where Col B matches 'Daily totals'!B5

Jan!B3:B400='Daily totals'!B5 gives you an array of True and False which is
converted to 1 and 0 by putting -- in front... corresponding elements of the
arrays you have within SUMPRODUCT are multiplied and then added...

So if you have a condition with -- in front you get COUNT... without a
condition you get SUM (of multiplications of corresponding elements)

 

[marsjune68]

Thank you soooooooo much. It worked!!!!! I wish I knew Excel better.
Formulas are a pain.

 

[Sheeloo]

You are most welcome...

Make them your friend...
Download Learn Excel 97 through Excel 2007 From MrExcel -
http://www.mrexcel.com/learnexcel2.shtml