Results 1 to 3 of 3

Thread: Sumproduct(and(if...

  1. #1
    Registered User
    Join Date
    Apr 2008
    Posts
    1
    Thanks
    0
    Thanked
    0 times in 0 posts

    Sumproduct(and(if...

    Hi all,

    Please Help!!!

    I'm really struggling to create possibly a basic formula....

    I need to create a simple sumproduct with multiple arrays.

    Column A = Model
    Column B = Country
    Column C = Price
    Column D = Inventory

    I need to calculate the Value of both Positive and Negative Inventory seperately
    I used to have a simple Sumproduct(if.. formula and only had inventory shown once per model.
    However, i've since populated the inventory column so that a figure is shown against all models whenever filtered against.

    Arrays
    a) Column B = "Total"
    b) Column D >0

    Calc:
    Column C x Column D

    Can anyone help? It's driving me nuts!!!

    Thanks

    Jeffuzz.

  2. #2
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: Sumproduct(and(if...

    You're better off with an array formula for this one. Basically the formula will be something like this

    SUM(IF(A:A="value1",IF(C:C>=0,"C:C",0),0))

    Then press Ctrl+Shift+Enter.

    What this does, is for each value in the column, it'll evaluate column A and check it's ok, then it'll check column C, to see whether it's above zero.

    Since you wanna do a Sumproduct, I *think* you put C:C*D in there, but it's been a while since I've had to write these so I'm a little bit rusty.

    (\___/) (\___/) (\___/) (\___/) (\___/) (\___/) (\___/)
    (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=)
    (")_(") (")_(") (")_(") (")_(") (")_(") (")_(") (")_(")


    This is bunny and friends. He is fed up waiting for everyone to help him out, and decided to help himself instead!

  3. #3
    Senior Member manwithnoname's Avatar
    Join Date
    Dec 2005
    Posts
    1,050
    Thanks
    17
    Thanked
    26 times in 25 posts

    Re: Sumproduct(and(if...

    can you just add 2 columns for Positive and Negative Inventory?
    Positive and Negative Inventory

    =IF(D1>0,C1*D1,0)

    =IF(D1<0,C1*D1,0)

    then sum these columns?

    EDIT:
    or what Lucio said, i'm not that impressed with excel but that quite neat

    I can get this to work ={SUM(IF(C1:C8>0,C1:C8*D18,0),0)} (ie select a range instead of a whole column)
    but not your whole column method using IF(A:A="value1" is there a "does this row have data in it test"? tried ISBLANK(A:A) as the test, it works for the row but when it enclosed by {} it returns #NUM
    Last edited by manwithnoname; 01-04-2008 at 07:57 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •