Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula and not sure where to start! Matching criteria. | Excel Worksheet Functions | |||
First populated cell in row array/ Last populated cell in row arra | Excel Worksheet Functions | |||
Array formula - sum with multiple criteria | Excel Discussion (Misc queries) | |||
Can I use an array formula with multiple criteria in the same row? | Excel Worksheet Functions | |||
Need formula that Counts items matching criteria using two columns | Excel Worksheet Functions |