View original (French)

Create a loop without VBA

lucky-luccio Posted messages 2 Status Membre -  
lucky-luccio Posted messages 2 Status Membre -
Hello,
I would like to write an Excel formula that performs the following calculation:

For i from A1 to A10
do =SUM(SUMPRODUCT((B1:B100=1)*(C1:C100=i)))
The range A1:A10 refers to values.

I'm pretty sure it can be done with a function without VBA, but how?
Thank you for your help.

3 réponses

Réponse 1 / 3
JvDo Posted messages 1924 Registration date   Status Membre Last intervention   859
 
Hello,

you can also do: 
=SUMPRODUCT((B1:B100=1)*(C1:C100=TRANSPOSE(A1:A10)))

to be validated by CTRL+SHIFT+ENTER

kind regards
2