SUMIF for multiple columns

[makku]

Hi,

I have the following situation:

Code Region1 Region2 Region1
XSAS $10.50 $11.50 $9.75

My worksheet contains many more columns! I need to sum the Code by Region as follows:

Code Region1 Region2
XSAS $20.25 $11.50


What is the right formula to use?


regards,

[Shijesh Kumar]

Hi,

Sumproduct function will do.

[oldchippy]

Hi,

Something like this may be

Region 1
=SUMPRODUCT(--(A2:A20="XSAS"),(B2:B20)+(D2:D20))

Region 2
=SUMPRODUCT(--(A2:A20="XSAS"),(C2:C20))

Depends on your layout of columns and number of rows?

[Rahul Nagar]

Hi Makku,

Try this...........


Code Region1 Region2 Region1 Region2
XSAS $10.50 $11.50 $9.75 $5.00

Code(SUM) Region1 Region2
XSAS =B5+D5 =C5+E5

Regards
Rahul

[Rahul Nagar]

Hi Makku,

Try this...........


Code Region1 Region2 Region1 Region2
XSAS $10.50 $11.50 $9.75 $5.00

Code(SUM) Region1 Region2
XSAS =B5+D5 =C5+E5

Check attached file.

Regards
Rahul

[sabunabu]

Hi,

I have the following situation:

Code Region1 Region2 Region1
XSAS $10.50 $11.50 $9.75

My worksheet contains many more columns! I need to sum the Code by Region as follows:

Code Region1 Region2
XSAS $20.25 $11.50


What is the right formula to use?


regards,

Depending on how many criteria you have, you can also use a sum with multiple (up to 7) IF statements. It's a Ctrl+Shift+Enter function.

={sum(if(A1:A25="XSAS",if(A1:Z1="Region 1",A2:Z2,0),0))}

Now for the ranges, you can input whatever you want depending on how big your range is. Hope this helps!