SUMIF - should be simple really??

[cruyff73]

Hi, can anyone help with this? Haven't used excel for a while but can't understand what's not working with this....

=SUMIF(A10:A35,"DSBI",G10:Y35)

Why would this formula only sum the amounts in column G and not the others?

Help much appreciated.

 

[Saba Sabaratnam]

SUMIF is not designed to total multiple columns.

You can use SUMPRODUCT as demonstrated below.

H1 formula = SUMPRODUCT(($B$1:$D$4)*($A$1:$A$4=G1))

Kind regards

Saba

 

[Saba Sabaratnam]

For your data, this will be the formula.

=SUMPRODUCT((G10:Y35)*(A10:A35="DSBI"))

Kind regards

Saba

 

[cruyff73]

For your data, this will be the formula.

=SUMPRODUCT((G10:Y35)*(A10:A35="DSBI"))

Kind regards

Saba

Great thanks Saba. Out of curiosity how would you do it if there was more than one criteria?

 

[AhoyNC]

It helps to let us know what version of Excel you are using.
If you have Excel 365 you could us the FILTER function.

=SUM(FILTER(G10:Y35,A10:A35="DSBI"))

 

[Saba Sabaratnam]

No problem

Suppose you have another AND criteria.You could enter use the following formula and replace the bold one with your argument.

=SUMPRODUCT((G10:Y35)*(A10:A35="DSBI")*(A10:A35="DSBI"))

Kind regards

Saba