SUM one column based on another column repeatedly

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

Guest

Consider the following:

Col A Col B Col C Col D
ERDF 45,678 JKNM 23,234
JKNM 12,909 ABCD 54,567
ABCD 22,222 ERDF 11,111

I want to search every column for ERDF and sum the column to the right of
it. Consider enough columns to reach column BZ.

Thanks,
casey
 
I just found an anomaly in this formula for my situation. I have numbers
also in the columns other than letters. And when they're all numbers, it
adds up erroneously. So reconsider the following:

Col A Col B Col C Col D
22 13 123 78
ERDF 12 HKHK 100
123 312 789 22
456 22 22 45

I need to add up the numbers in columns B & D based on the numbers (account
numbers) in columns A and C. In your previous formula, if summing for
account number "22", I would get an answer of 80 instead of 58 (It adds
column C because it found a "22" in column B. I hope that's clear.

Thanks,
casey
 
Ok, that makes it more complicated.

Try this:

=SUMPRODUCT((MOD(COLUMN(A1:C4),2)=1)*(A1:C4=22),B1:D4)

Notice there is still a stagger in the ranges.
 
Perfect! Next step for me is to break down what your formula and understand
why.

Thanks a million!!!
casey
 
Fix

Try this.


=SUM(SUMIF(A:A,A1,B:B),SUMIF(C:C,A1,D:D))

That should fix your error.

Marty

Another way of doing this is ....

=SUM(SUMIF(A1:A3,"ERDF",B1:B3),SUMIF(C1:C3,"ERDF",D1:D3))
 
Last edited:
You're welcome!

--
Biff
Microsoft Excel MVP


casey said:
Perfect! Next step for me is to break down what your formula and
understand
why.

Thanks a million!!!
casey
Click to expand...
 
Back
Top