| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will not give accurate totals.
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... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Will not give accurate totals.
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) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "marsjune68" wrote: 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... |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Will not give accurate totals.
Thank you soooooooo much. It worked!!!!! I wish I knew Excel better.
Formulas are a pain. "Sheeloo" wrote: 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) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "marsjune68" wrote: 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... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Will not give accurate totals.
You are most welcome...
Make them your friend... Download Learn Excel 97 through Excel 2007 From MrExcel - http://www.mrexcel.com/learnexcel2.shtml "marsjune68" wrote: Thank you soooooooo much. It worked!!!!! I wish I knew Excel better. Formulas are a pain. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot Totals: Group totals different from Grand totals | Excel Discussion (Misc queries) | |||
| HOW DO I SETUP A PAGE IN EXCEL TO GIVE TOTALS TO ANOTHER PAGE | Excel Worksheet Functions | |||
| Give RELEVANT responses to questions. DO NOT give usless list | Excel Worksheet Functions | |||
| Totals of calculated field in pivot table give incorrect results | Excel Worksheet Functions | |||
| This formula is also summing the the value above to give 2 totals. | Excel Worksheet Functions | |||
Linear Mode
