View original (French)

SUMIFS function for 2 criteria two-way table

Solved
tsetsebcn Posted messages 8 Status Member -  
 Britanian -
Hello,

I am desperately looking for a formula that would allow me to do something like SUMIF but with two criteria on a range (table) with 2 entries. Is this possible?

In my specific case, and see the example in the image, I would like to obtain the seller's target in the yellow cell based on the month and the seller.

Thank you in advance for your help
Sebastien

2 answers

  1.
    Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 785
     
    Hello,

    Starting from Excel 2007, you can use SUMIFS()
    Before that, you can use SUMPRODUCT()

    --
    Best regards
    Patrice
    0
    1. tsetsebcn Posted messages 8 Status Member 1
       
      Thank you Patrice for the response. I will take Via's proposal which is all ready :)
      0
  2. via55 Posted messages 14395 Registration date   Status Member Last intervention   2 759
     
    Hello to both of you

    In your example, it's not a sum that you apparently want but a value from a matrix based on the abscissa and the ordinate
    In this case, you need to use the combination INDEX MATCH

    =INDEX(range of objectives; MATCH(salesperson name or cell address with name; vertical range of the 3 salespeople; 0); MATCH(month sought or cell address with month; horizontal range of the 12 months; 0))

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    0
    1. tsetsebcn Posted messages 8 Status Member 1
       
      Hello Via, thank you very much for this answer and this help. It works perfectly!!!!!
      1
    2. Britanian
       

      Perfect for me as well

      With a little SIERREUR in front for when my references are empty and it's beautiful =)

      0