SUM one column based on another column repeatedly
 

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

SUM one column based on another column repeatedly
 

Try this:

=SUMIF(A1:C3,"ERDF",B1:D3)

Notice how the ranges are staggered!

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
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

SUM one column based on another column repeatedly
 

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

Mucho Thanks!!!
casey

"T. Valko" wrote:

Try this:

=SUMIF(A1:C3,"ERDF",B1:D3)

Notice how the ranges are staggered!

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
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

SUM one column based on another column repeatedly
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
Perfect! And so simple. I was trying to write a SUMIF using OFFSET.

Mucho Thanks!!!
casey

"T. Valko" wrote:

Try this:

=SUMIF(A1:C3,"ERDF",B1:D3)

Notice how the ranges are staggered!

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
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

SUM one column based on another column repeatedly
 

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" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
Perfect! And so simple. I was trying to write a SUMIF using OFFSET.

Mucho Thanks!!!
casey

"T. Valko" wrote:

Try this:

=SUMIF(A1:C3,"ERDF",B1:D3)

Notice how the ranges are staggered!

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
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

SUM one column based on another column repeatedly
 

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.

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
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" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
Perfect! And so simple. I was trying to write a SUMIF using OFFSET.

Mucho Thanks!!!
casey

"T. Valko" wrote:

Try this:

=SUMIF(A1:C3,"ERDF",B1:D3)

Notice how the ranges are staggered!

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
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

SUM one column based on another column repeatedly
 

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

Thanks a million!!!
casey

"casey" wrote:

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

SUM one column based on another column repeatedly
 

You're welcome!

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
Perfect! Next step for me is to break down what your formula and
understand
why.

Thanks a million!!!
casey

"casey" wrote:

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