Summing a total from a criteria (string)

[GarysBeau]

Hi new to this (so apologies if I do this the wrong way)

I had a look around the forum for similiar topics and whilst some were similiar didn't seem to answer my query.

Anyway my query is:

I have lots of data in the form of spreadsheets with lots of tabs to sum but I cant add columns, reformat or change this initial data. However I am applying a cover sheet with the forumals to work out the totals based on this format. Basically the format is like this:

_____A______B______C_____D_____
1_ "Sat 1"___10______5_____5_____
2_"Sun 2"____5______4_____1______
3_"Mon 3"____2______2_____1______
4_"Tue 4"____7______3_____3____
5_"Wed 5"____1______2_____9___
6_"Thur 6"___8_______7_____11___
7_"Fri 7"_____1_______1_____1____
8_"Sat 8"____3_______3_____3____

I want to sum all the days which have saturdays (in fact each day of the week as a separate total), based on a range of data. So I ultimately get the result for Sat as: 29 (10+5+5+3+3+3).

i've tried everything from SUMIF, IF with Left functions) etc but having no joy, so any help really appreciated.

Hope this makes sense.

Regards

 

[T. Valko]

Hi new to this (so apologies if I do this the wrong way)

I had a look around the forum for similiar topics and whilst some were similiar didn't seem to answer my query.

Anyway my query is:

I have lots of data in the form of spreadsheets with lots of tabs to sum but I cant add columns, reformat or change this initial data. However I am applying a cover sheet with the forumals to work out the totals based on this format. Basically the format is like this:

_____A______B______C_____D_____
1_ "Sat 1"___10______5_____5_____
2_"Sun 2"____5______4_____1______
3_"Mon 3"____2______2_____1______
4_"Tue 4"____7______3_____3____
5_"Wed 5"____1______2_____9___
6_"Thur 6"___8_______7_____11___
7_"Fri 7"_____1_______1_____1____
8_"Sat 8"____3_______3_____3____

I want to sum all the days which have saturdays (in fact each day of the week as a separate total), based on a range of data. So I ultimately get the result for Sat as: 29 (10+5+5+3+3+3).

i've tried everything from SUMIF, IF with Left functions) etc but having no joy, so any help really appreciated.

Hope this makes sense.

Regards

Maybe this...

=SUMPRODUCT((LEFT(A1:A8,3)="Sat")*B1:D8)

 

[GarysBeau]

Maybe this...

=SUMPRODUCT((LEFT(A1:A8,3)="Sat")*B1:D8)

You beauty, I could kiss you! Virtually of course! Thanks a lot.

 

[Trevor G]

Ive look to use a couple of formula to find the day of the week then sumif the number of the day of week

Excel Workbook
	H	I	J	K
4	Date	Day	Amount	Week Day Number
5	01/01/2011	Saturday	23	7
6	02/01/2011	Sunday	24	1
7	03/01/2011	Monday	25	2
8	04/01/2011	Tuesday	26	3
9	05/01/2011	Wednesday	27	4
10	06/01/2011	Thursday	28	5
11	07/01/2011	Friday	29	6
12	08/01/2011	Saturday	30	7
13	09/01/2011	Sunday	31	1
14	10/01/2011	Monday	32	2
15	11/01/2011	Tuesday	33	3
16	12/01/2011	Wednesday	34	4
17	13/01/2011	Thursday	35	5
18	14/01/2011	Friday	36	6
19	15/01/2011	Saturday	37	7
20	16/01/2011	Sunday	38	1
21	17/01/2011	Monday	39	2
22	18/01/2011	Tuesday	40	3
23	19/01/2011	Wednesday	41	4
24	20/01/2011	Thursday	42	5
25
26		Saturday	90
27		Sunday	93
28		Monday	96
29		Tuesday	99
30		Wednesday	102
31		Thursday	105
32		Friday	65
Sheet1

 

[T. Valko]

You beauty, I could kiss you! Virtually of course! Thanks a lot.

You're welcome. Thanks for the feedback!