-2

I am trying to sum the amounts in column B based on the types of symbols in column A. Any symbol with "EW" at the start needs to be grouped and by date month - see column D. The second symbol comes in two formats but also needs to be grouped, so "OES" and "OMSX" needs to be grouped together and with their date month. I know I need wildcards here but I cannot get this to work.

EDIT, correct EW, accidentally had "EWS" before, apologies to anyone who responded

enter image description here

Rich Stevens
  • 599
  • 4
  • 17

1 Answers1

0

Thius should work if you can get your data corrected.

=SUMIFS(B:B, A:A, REPLACE(D2, FIND(" ", D2), LEN(D2), "*"), A:A, REPLACE(D2, 1, FIND(" ", D2), "*")&"*")
  • Thanks for your help, this works for the EW ones but not the others. I was thinkning that the symbols being within the actual formula rather than referencing the cells in column D would be better. Because, this one cannot be referenced: "OES & OMSX Jan". They need to be lumped together but don't share the same 3-4 letters. – Rich Stevens Feb 14 '19 at 02:43
  • If you insist on a formula approach, I see no reason why you couldn't modify the above for duel symbols. –  Feb 14 '19 at 02:59
  • I have tried to do this but I cannot. Thanks for the help, this solution is much better than what I had originally. – Rich Stevens Feb 14 '19 at 05:43