Need help with SUM formula matching 1st Array criteria in 2nd Arra
 

Hi Folks,

I'm hoping someone can help please.

In Cell A, I need to SUM total the values from Col 3 in Range 2, based on
each value in Col 1 of Range 2 which matches the respective/relevant values
in Col 1 of Range 1.

Or put another way. I have Col 1 of Range 1 which has intermittent values.
Each time a value is found in Range 1, I need match that to a value in Col 1
of Range 2, return the value from Col 3 of Range 2 and then in a separate
cell, SUM all of the (Col 3, Range 2) returned values.

I have tried various forms of Array formulas, but can't seem to hit on it.
Can anyone help please?

Need help with SUM formula matching 1st Array criteria in 2nd Arra
 

If you have names in Col A (A1:A100) and amounts in Col B (B1:B100)

then this will give you the total in Col B where name is Adam
=SUMPRODUCT(--(A1:A100="Adam"),(B1:B100))

"K" wrote:

Hi Folks,

I'm hoping someone can help please.

In Cell A, I need to SUM total the values from Col 3 in Range 2, based on
each value in Col 1 of Range 2 which matches the respective/relevant values
in Col 1 of Range 1.

Or put another way. I have Col 1 of Range 1 which has intermittent values.
Each time a value is found in Range 1, I need match that to a value in Col 1
of Range 2, return the value from Col 3 of Range 2 and then in a separate
cell, SUM all of the (Col 3, Range 2) returned values.

I have tried various forms of Array formulas, but can't seem to hit on it.
Can anyone help please?

Need help with SUM formula matching 1st Array criteria in 2nd
 

Unfortunately, that won't work.

I have names in Sheet 2!Col A and weights in Sheet 2!Col C.

I also have names in Sheet 1!Col A.

The Sheet 1 range is nothing like the same size as the Sheet 2 range and has
data only intermittently.

I need to "walk" Sheet 1!Col A, every time I find a value, look up that
value in Sheet 2!Col A and return the value in Sheet 2!Col C, and then sum up
all the corresponding returned values in Sheet 1!Cell Z.

I'm pretty sure this can be done in a simple formula without resorting to
hidden columns and macros, etc. I just haven't figured out how to do it.

Someone here does, I'm sure. Are you out there? PLEASE! :-)

"Sheeloo" wrote:

If you have names in Col A (A1:A100) and amounts in Col B (B1:B100)

then this will give you the total in Col B where name is Adam
=SUMPRODUCT(--(A1:A100="Adam"),(B1:B100))

Need help with SUM formula matching 1st Array criteria in 2nd
 

Assuming you have a name in A1 of Sheet1 then enter this in Z1 of Sheet1


=SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100))

You can copy this formula down

You can sum up col B if you want to get the sum for all names in Sheet1


"K" wrote:

Unfortunately, that won't work.

I have names in Sheet 2!Col A and weights in Sheet 2!Col C.

I also have names in Sheet 1!Col A.

The Sheet 1 range is nothing like the same size as the Sheet 2 range and has
data only intermittently.

I need to "walk" Sheet 1!Col A, every time I find a value, look up that
value in Sheet 2!Col A and return the value in Sheet 2!Col C, and then sum up
all the corresponding returned values in Sheet 1!Cell Z.

I'm pretty sure this can be done in a simple formula without resorting to
hidden columns and macros, etc. I just haven't figured out how to do it.

Someone here does, I'm sure. Are you out there? PLEASE! :-)

"Sheeloo" wrote:

If you have names in Col A (A1:A100) and amounts in Col B (B1:B100)

then this will give you the total in Col B where name is Adam
=SUMPRODUCT(--(A1:A100="Adam"),(B1:B100))

Need help with SUM formula matching 1st Array criteria in 2nd
 

Sheeloo,

thanks for you suggestions so far, but unfortuantely that way requires me to
use another column again to do interim memory value storage, which is what
I'm trying to avoid.

Basically, what I would like to have happen is, if in your example;

=SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100))

Sheet2!A1:A100=A1 could be replaced with Sheet2!A1:A100=Sheet1!A1:A60 and
have it work for the entire range.

"Sheeloo" wrote:

Assuming you have a name in A1 of Sheet1 then enter this in Z1 of Sheet1

=SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100))

You can copy this formula down

You can sum up col B if you want to get the sum for all names in Sheet1