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


    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


    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


    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!