summing based on substring

  • Thread starter Thread starter David Schwartz
  • Start date Start date
D

David Schwartz

I receive a xls weekly with hit data for a web site. The data are in
the following form (note: there's 2 cols):

abcfoo 2
abcbar 6
deffoo 4
defbar 1
ghibar 2

I need to sum up the data based on the last 3 characters (bar and foo
above). Ideally, I'd like two new cols where each row would correspond
to a 'bar', 'foo', or whatever, column 1 containing 'bar', 'foo', etc
and the 2nd column being the sum for that substring.

Any hints for doing this would be greatly appreciated!!

TIA,
David
 
=SUMPRODUCT(--(RIGHT(A1:A5,3)="bar"),B1:B5)
=SUMPRODUCT(--(RIGHT(A1:A5,3)="foo"),B1:B5)
 
=SUMIF(A:A,"*bar",B:B)

=SUMIF(A:A,"*foo",B:B)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top