SUM one column based on another column repeatedly

[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

 

[T. Valko]

Try this:

=SUMIF(A1:C3,"ERDF",B13)

Notice how the ranges are staggered!

 

[Guest]

Perfect! And so simple. I was trying to write a SUMIF using OFFSET.

Mucho Thanks!!!
casey

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Guest]

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

 

[T. Valko]

Ok, that makes it more complicated.

Try this:

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

Notice there is still a stagger in the ranges.

 

[Guest]

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

Thanks a million!!!
casey

 

[mdschuetz]

Fix

Try this.


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

That should fix your error.

Marty

Another way of doing this is ....

=SUM(SUMIF(A1:A3,"ERDF",B1:B3),SUMIF(C1:C3,"ERDF",D13))

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

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

Thanks a million!!!
casey