View original (French)

SUMIF with "sum range across two columns"

Solved
sosotissmey225 Posted messages 8 Status Membre -  
Vaucluse Posted messages 27336 Registration date   Status Contributeur 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 réponse

Réponse 1 / 1
Vaucluse Posted messages 27336 Registration date   Status Contributeur 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
sosotissmey225 Posted messages 8 Status Membre
 
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
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453
 
and "Reference" what is it?
0