Sumif with multiple Sum Range

I needs to get the Total invoice value in column BH with my sum ranges is in column AZ + column BB to column BG with criteria as column I.


- Abhinav

Its long but it works

Please Login or Register  to view this content.

edit : should have said put it in BH3 and drag down

Originally Posted by abhinavbinkar

It is working.

Can we do it by Sumproduct function to shorten length of formula?

Not to make it shorter I don't think. You would need to replace each
SUMIF($I$3:$I$38,I3,$AZ$3:$AZ$38) with
SUMPRODUCT(--($I$3:$I$38=I3),$AZ$3:$AZ$38)

which is longer.

You can't combine them all together in one sumproduct either as you want to add across the columns not multiply across the columns that match your invoice number.

Hi,

In BH3:

=SUMPRODUCT(($I$3:$I$38=I3)*N(+$AZ$3:$BG$38))

and copied down.

Regards

Originally Posted by Crooza

You can't combine them all together in one sumproduct either as you want to add across the columns not multiply across the columns that match your invoice number.

Actually SUMPRODUCT is capable of this. See my answer.

Originally Posted by abhinavbinkar

Thank you so much sir. This also worked perfectly.

You're welcome!

Dear Abhinav Binkar : While going through your attach file, i have notice that, you are attach your original sensitive data.

You can edit your opening post - go advanced - manage attachments and remove the file yourself.