# More Sum If Help Please

• SO in cloumn A i have country codes and Column B i have prices.
Example,

[TABLE="width: 128"]

[tr]

[TD="width: 64"]Mex[/TD]
[TD="width: 64, align: right"]10[/TD]

[/tr]

[tr]

[td]

usa

[/td]

[TD="align: right"]10[/TD]

[/tr]

[tr]

[td]

mex

[/td]

[TD="align: right"]-5[/TD]

[/tr]

[tr]

[td]

usa

[/td]

[TD="align: right"]-5[/TD]

[/tr]

[tr]

[td]

mex

[/td]

[TD="align: right"]3[/TD]

[/tr]

[tr]

[td]

usa

[/td]

[TD="align: right"]3[/TD]

[/tr]

[tr]

[td]

mex

[/td]

[TD="align: right"]-4[/TD]

[/tr]

[tr]

[td]

usa

[/td]

[TD="align: right"]-4[/TD]

[/tr]

[/TABLE]

I need four formulas that work, One that sums mexico > 0 , mexico < 0, Usa > 0 , and Usa < 0

How would i format this sumif function or is that even the right formula to use?

My attempt was =sumif(A1:B8,"mex",sumif(A1:B8,>0,B1:B8)) Which did absolutely nothing for me...

Please and thank you!

• Re: More Sum If Help Please

Are you using Excel 03 or higher version?

• Re: More Sum If Help Please

03

• Re: More Sum If Help Please

Perhaps.

=SUMPRODUCT((\$A\$2:\$A\$20="mex")+(\$A\$2:\$A\$20="usa")*(\$B\$2:\$B\$20))

Regards

Fotis :hammerhe:

[SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

[SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

[SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!

[SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

• Re: More Sum If Help Please

Suppose your two line is column A & B,
just type any cell=SUMIF(A:A,A1,B:B) press enter.

Or,

=SUMIF(A:A,A1,B:B)+SUMIF(A:A,A2,B:B)

"A1 for Mex"
"A2 for Usa".

• Re: More Sum If Help Please

=SUMIF(A:A,A1,B:B) so this seems to work for mexico but i then need a second criterion in the same formula that add only the positive values (>0)

• Re: More Sum If Help Please

Post#4 suggestion?

Regards

Fotis :hammerhe:

[SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

[SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

[SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!

[SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

• Re: More Sum If Help Please

For post #4 that combines everything i need my sums separate essentially i need four different formulas that will give me the sums described in my first post
Mex and positive
Mex and negative
And so on

• Re: More Sum If Help Please

=SUMPRODUCT((\$A\$2:\$A\$20="mex")*(\$B\$2:\$B\$20>0)*(\$B\$2:\$B\$20)) :question:

Regards

Fotis :hammerhe:

[SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

[SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

[SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!

[SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

• Re: More Sum If Help Please

Quote from Fotis1991;681795

=SUMPRODUCT((\$A\$2:\$A\$20="mex")*(\$B\$2:\$B\$20>0)*(\$B\$2:\$B\$20)) :question:

Perfect!!

Can you explain to me what this formula is doing? how does it know to only add the last column if you have so many arrays im not familiar witht he sum product function.

• Re: More Sum If Help Please

Also
=SUM(IF(\$A\$2:\$A\$20="mex",IF(B\$2:\$B\$20>0,\$B\$2:\$B\$20)))
Confirm Control+shift+Enter

• Re: More Sum If Help Please

Quote from Zpichette;681814

Perfect!!

Can you explain to me what this formula is doing? how does it know to only add the last column if you have so many arrays im not familiar witht he sum product function.

=SUMPRODUCT((\$A\$2:\$A\$20="mex")*(\$B\$2:\$B\$20>0)*(\$B\$2:\$B\$20))

In simple words, formula says:

Look in range A2:A20 for the string "mex"((\$A\$2:\$A\$20="mex")). If you find some results, then look in range B2:B20 and see in which rows (that in column A contains "mex") the number is > from 0(\$B\$2:\$B\$20>0). Then add only these values((\$B\$2:\$B\$20)).

Bob's Philips site is an excellent drive to learn everything for SUMPRODUCT.

Regards

Fotis :hammerhe:

[SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

[SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

[SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!

[SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!