A Loop within a loop without VBA/Helper columns/vloolups

[aes89]

Hello All,

I want to achieve the below:

Array A : 20 items
Array B : 300 items
Perform a process

For each item in Array A, search them up in Array B and perform an addition with a constant and sum the results in 1 cell.


Is it possible to achieve that with array formulas on the spreadsheet without having to resort to VBA, vlookups, or helper columns? I have searched most tutorials on the net, but no luck.

Thanks if anyone could help or clarify if this is even possible.

Aes89

 

[baitmaster]

welcome to the board

please can you be more specific. What do you mean by "search them up"? And what process do you want to perform?

 

[aes89]

Hello,

Thank you for the reply. I tried looking up tutorials on arrays, but they seemed to focus on mathematical operations on a single array (ie. for each element in Array One, subtract 10).

To be more specific, I have a list of 20 reference numbers in one column and a second column containing 350 reference entries. For each reference number in the first column, I would like to count how many times the reference number appears in the 350 reference entries. And finally, I would like to sum up in one cell, the entire count process and total for all the 20 reference numbers in the first column.

Would like to do all the above in one cell via array formulas. Thanks in advance for any help.

aes89

 

[baitmaster]

So, for each item in list 1, you want to do a Countif[in list 2], then sum all the countifs... but you want to do this in one single formula

You could simply hard-code 20 countifs, and wrap the sum around them, but its not very elegant or flexible for say 25 refs

Ideally you want a single dynamic formula that does this, I'm sure it exists, but whether I can come up with it I don't know. Will give it some more thought, and throw it open to others now I think we know what you're looking for...

 

[baitmaster]

aw, it wasn't too bad once I actually thought about it

=SUM(COUNTIF($D$34:$D$55,$A$34:$A$40)) entered as an array formula shift + ctrl + enter. Change your ranges as necessary