View Full Version : Accumulating data by days of the week
legepe
07-29-2006, 03:39 PM
Hi,
I have a worksheet with columns labled in the top cell the days of the week, below this I have values for that day.
I want to accumulate the values for each day and total them in a different sheet.
Is there a way that I can do this using a formula so that it will recognise the each day and add the values in one cell
Any help would be great
legepe
Bob Phillips
07-29-2006, 04:22 PM
Hi,
I have a worksheet with columns labled in the top cell the days of the week, below this I have values for that day.
I want to accumulate the values for each day and total them in a different sheet.
Is there a way that I can do this using a formula so that it will recognise the each day and add the values in one cell
Any help would be great
legepe
=SUMIF(1:1,"Monday",2:2)
legepe
07-31-2006, 08:27 AM
Hi again,
I have tried modifying it so it will work... but it just keeps displaying -"NAME"
=SUMIF(A1:AH1,"Mon",A7:AH7)
Is there something obvious that is wrong with this modification to the formula?
Thanks
legepe
lenze
07-31-2006, 08:50 AM
Rearrange your data into 2 columns.
"DAY" and AMT"
Now use a Pivot Table.
lenze
Shazam
07-31-2006, 08:55 AM
Hi again,
I have tried modifying it so it will work... but it just keeps displaying -"NAME"
=SUMIF(A1:AH1,"Mon",A7:AH7)
Is there something obvious that is wrong with this modification to the formula?
Thanks
legepe
See if this helps.
=SUMPRODUCT((A1:AH1="Mon")*(A7:AH7))
legepe
07-31-2006, 09:28 AM
I used the formula that you posted but I still cannot get it to work
I?ve posted part of the worksheet, Hope you can help?
Thanks
legepe
Shazam
07-31-2006, 09:40 AM
Need to adjust ranges.
=SUMPRODUCT((E1:AH1="Mon")*(E7:AH7))
Bob Phillips
07-31-2006, 10:48 AM
Hi again,
I have tried modifying it so it will work... but it just keeps displaying -"NAME"
=SUMIF(A1:AH1,"Mon",A7:AH7)
Is there something obvious that is wrong with this modification to the formula?
Thanks
legepe
Try
=SOMAR.SI(E1:AH1;"Mon";E7:AH7)
legepe
07-31-2006, 11:09 AM
Hi
Tried the formula and it shows up as an error
=SOMAR.SI(E1:AH1;"Mon";E7:AH7)
I do not know how to modify this? can any one help??
legepe
Bob Phillips
07-31-2006, 11:11 AM
Hi
Tried the formula and it shows up as an error
=SOMAR.SI(E1:AH1;"Mon";E7:AH7)
I do not know how to modify this? can any one help??
legepe
I took a punt that you were using Spanish Excel (Hoja?), guess I was wrong.
Or maybe it should have been SUMAR.SI?
legepe
07-31-2006, 11:26 AM
You were right, I am using spanish excel
It is SUMAR.SI but it still shows up as an error in Range?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.