daily total before the next date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have gone through many pages spending much time in this discussion, looking for a solution but didn't find any near to what I want.

I am looking for a formula to sum up a list of values for different groups for the day.

It's something like this:-
If A1 is date, sum B1:last input (B3 for 4-Jun) in C3, while C1 and C2 remain blank. the same goes with C7, E3 and E7, baring in mind there are variable entries for different days.

A B C D E
1 4/6 25 14
2 60 30
3 60 ___ 30 ___
4 5/6 25 10
5 25 15
6 25 15
7 60 ___ 30 ___
8
9

Please help. Very much appreciated.
Thank you.
 
Hi
if column A really has real date values use the following
formula in cell C1:
=IF(OR(AND(A1="",A2<>""),B2=""),SUM(INDIRECT("B" & MATCH
(9.99999999999999E+307,$A$1:A1) & ":B" & ROW())),"")

copy this formula down a far as needed
-----Original Message-----
Hi,
I have gone through many pages spending much time in this
Click to expand...
discussion, looking for a solution but didn't find any
near to what I want.
I am looking for a formula to sum up a list of values for different groups for the day.

It's something like this:-
If A1 is date, sum B1:last input (B3 for 4-Jun) in C3,
Click to expand...
while C1 and C2 remain blank. the same goes with C7, E3
and E7, baring in mind there are variable entries for
different days.
 
C1, copied down:

=IF((A1="")*(A2<>"")+(B2=""),SUM(INDEX(B:B,MATCH(9.99999999999999E+307,$A$1:
A1)):B1),"")

E1, copied down:

=IF((A1="")*(A2<>"")+(D2=""),SUM(INDEX(D:D,MATCH(9.99999999999999E+307,$A$1:
A1)):D1),"")

FangYR said:
Hi,
I have gone through many pages spending much time in this discussion,
Click to expand...
looking for a solution but didn't find any near to what I want.
I am looking for a formula to sum up a list of values for different groups for the day.

It's something like this:-
If A1 is date, sum B1:last input (B3 for 4-Jun) in C3, while C1 and C2
Click to expand...
remain blank. the same goes with C7, E3 and E7, baring in mind there are
variable entries for different days.
 
Frank Kabel,
I got a 5 digit answer in C3, C4, C5 etc., instead of 145 (C3). Something went wrong somewhere.
--
FangYR


Frank Kabel said:
Hi
if column A really has real date values use the following
formula in cell C1:
=IF(OR(AND(A1="",A2<>""),B2=""),SUM(INDIRECT("B" & MATCH
(9.99999999999999E+307,$A$1:A1) & ":B" & ROW())),"")

copy this formula down a far as needed
-----Original Message-----
Hi,
I have gone through many pages spending much time in this
Click to expand...
discussion, looking for a solution but didn't find any
near to what I want.
I am looking for a formula to sum up a list of values for different groups for the day.

It's something like this:-
If A1 is date, sum B1:last input (B3 for 4-Jun) in C3,
Click to expand...
while C1 and C2 remain blank. the same goes with C7, E3
and E7, baring in mind there are variable entries for
different days.
Click to expand...
 
Frank Kabel,
Many appologies, I place the tables into the wrong columns causing weird answers.
everythings' fine now.
Big thank you .

And you too, Aladin Akyurek, thank you.
Regards
 
Hi
what do you mean with '5 digit entries' Can you post an
example of the wron result


-----Original Message-----
Frank Kabel,
I got a 5 digit answer in C3, C4, C5 etc., instead of 145
Click to expand...
(C3). Something went wrong somewhere.
 
Hi Aladin
quite similar appraoch (though I would assume INDEX is
faster than INDIRECT -> so your's is probably faster).

One thing I also missed in my formula:
I would change the condition check to

IF(((A1="")*(A2<>""))+((B2="")*(B1<>"")),..
to make sure that after the last entry no calculation
happens
 
Hi, Frank Kabel and Aladin Akyurek,
Thanks again for the solution but I"ve got one more problem. I cannot do a total (=sum) at the bottom of the column.
 
Hi
why not :-)
What error did you encounter?
-----Original Message-----
Hi, Frank Kabel and Aladin Akyurek,
Thanks again for the solution but I"ve got one more
Click to expand...
problem. I cannot do a total (=sum) at the bottom of the
column.this discussion, looking for a solution but didn't find
any near to what I want.while C1 and C2 remain blank. the same goes with C7, E3
and E7, baring in mind there are variable entries for
different days.
 
Hi
I provided a small correction (as a follow up to Aladin's formula).
Try:
=IF(OR(AND(A1="",A2<>""),AND(B2="",B1<>"")),SUM(INDIRECT("B" & MATCH
(9.99999999999999E+307,$A$1:A1) & ":B" & ROW())),"")
 
Hi, Frank,
YES, it works, my silly mistake again. I've forgotten I added a header row.
Thanks for your time and patience.
--
FangYR


FangYR said:
Hi
I use your lastest formula and this time i got #REF for an answer.
Click to expand...
 
Back
Top