Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Thursday, 08 December 2022
  3 Replies
  3.6K Visits
0
Votes
Undo
i want to set formula in excel which data is below please help me to set formula ....i have attached excel sheet also.
---------------------------------------------------------------------------------
RECV DATE | RCV. CH. NO | RECV | RECV DATE | RCV. CH. NO | RECV |
---------------------------------------------------------------------------------
02-Aug | 1362 | 717 | 02-Aug | 1366 | 612 |
---------------------------------------------------------------------------------

my data is in column wise and i want datewise total sum of "RECV" values ...

please help me
Attachments (1)
1 year ago
·
#3313
-1
Votes
Undo
Hi there,

Sorry for the late reply.

Since your data is in column wise, you will have to nest some formulas together:

In the cell O4, the formula can be: =SUM(SUM(FILTER(F4:F6,D4:D6=N4,0)),SUM(FILTER(I4:I6,G4:G6=N4,0)),SUM(FILTER(L4:L6,J4:J6=N4,0)))
In the cell O5, the formula can be: =SUM(SUM(FILTER(F4:F6,D4:D6=N5,0)),SUM(FILTER(I4:I6,G4:G6=N5,0)),SUM(FILTER(L4:L6,J4:J6=N5,0)))
In the cell P4, the formula can be: =TEXTJOIN(",",TRUE,TEXTJOIN(",",TRUE,FILTER(E4:E6,D4:D6=N4,"")),TEXTJOIN(",",TRUE,FILTER(H4:H6,G4:G6=N4,"")),TEXTJOIN(",",TRUE,FILTER(K4:K6,J4:J6=N4,"")))
In the cell P5, the formula can be: =TEXTJOIN(",",TRUE,TEXTJOIN(",",TRUE,FILTER(E4:E6,D4:D6=N5,"")),TEXTJOIN(",",TRUE,FILTER(H4:H6,G4:G6=N5,"")),TEXTJOIN(",",TRUE,FILTER(K4:K6,J4:J6=N5,"")))

Please give it a try.

Amanda
Attachments (1)
HI,
Thanks for your reply...really awesome formula you are using in it....but it is not much more useful ...resion behind of it ....here no. of column is not fixed..it can increase any time....and secondary resion is no one is using ms office 360 or upgraded version....here all have only ms office 2013 and in this text joint formula is not avilable.....so please suggest another formula which is capable for ms 2013...
1 year ago
·
#3316
0
Votes
Undo
Hi there,

If the number of columns is not fixed, then I'm afraid it's not gonna work with formulas...

Amanda
  • Page :
  • 1
There are no replies made for this post yet.