View original (French)

SUMIF with "sum range across two columns"

Solved
sosotissmey225 Posted messages 8 Status Member -  
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   -
Hello,

I have a SUMIF formula that works well, but instead of putting the "sum range" on one column, I want the sum of two columns.

My attempts with SUMIF + SUMPRODUCT have been unsuccessful...

Thank you for your help.

1 answer

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello

    SUM.IF only considers one column at a time, so you need to combine them with this function

    SUM.IF(1st...) + SUM.IF(2nd...)

    or you can try with:

    =SUMPRODUCT((code field="searched code")*(range of amounts to sum))

    Row numbers are mandatory with this function

    example

    the references in A1:A10 value on line "Ref" to sum
    the amounts to sum in B1:C10:

    =SUMPRODUCT((A1:A10="ref")*(B1:C10))

    or if your columns are not adjacent:

    =SUMPRODUCT((A1:A10="ref")*(B1:B10+C1:C10))

    regards

    The quality of the answer mainly depends on the clarity of the question, thank you!
    6
    1. sosotissmey225 Posted messages 8 Status Member
       
      I tried on your previously sent file by adding a column amount in column C, but I only get 0 with the following formula: =SUMPRODUCT((A1:A10="Reference")*(B1:C10))
      0
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      and "Reference" what is it?
      0