View original (French)

Create a loop without VBA

lucky-luccio Posted messages 2 Status Member -  
lucky-luccio Posted messages 2 Status Member -
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 answers

  1.
    JvDo Posted messages 1924 Registration date   Status Member 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