Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Question Verifying Certain Multiple Conditions Exist in an Array

    I have some large customer tables which have discounting information in them. The format of the tables is:

    A B C D
    Customer Number Condition Type Material/Variant Amount
    20004524 ZSTA MNDGY327 -35
    20004525 ZSTX NTFDE4765 -25
    20004525 ZTFA 264587 -30



    I currently an using SUMPRODUCT on the Calculation Sheet, based on the 4 input values, to bring in the amounts based on the references: CUSTNUMBER, CONDNUMBER, VARNUMBER & AMTNUMBER, which are simply columns A, B, C & D, respectively. This works great for price calculation.

    The problem is when the value on the Calculation Sheet is "0.00", there's no way to tell if the discount is actually loaded in the table at 0.00 (this happens sometimes) or if there is no discount loaded in the table. I'd like to add a cell to the calculation sheet that will verify for the user whether it is already loaded at "0" (no message) or if it's missing (i.e. " Please load missing ZSTA condition for this account"), etc.

    I would assume that this would be a variation of what I'm already using but not certain. Any help on this would be most appreciated.
    Attached Thumbnails Attached Thumbnails Test.jpg  
    Last edited by jj023; 02-22-13 at 10:48.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    Use the COUNTIF function to look for empty or zero-value cells?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Feb 2013
    Posts
    3
    COUNTIF does no good. I have thousands of customer numbers, dozens of condition types, thousands of materials/variants and an infinite number of amounts.

    What I need to know is when the Discount appears as 0.00% on the calculation sheet, is it because that particular customer, particular material/variant and particular discount condition is actually loaded at "0.00%" or is the condition missing from that customer number, material/variant, discount type. There are 3 variables that have to be checked to obtain the amount, which is why I have to use SUMPRODUCT to obtain the amount.

    COUNTIF only tells me if one particular condition is met.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    COUNTIF will tell you if a record exists in a table that has zero in a given column, though. However, looking for records that aren't there in an Excel worksheet is rather tricky.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Feb 2013
    Posts
    3

    Smile

    Thank you WEEJAS,

    I actually found a way using COUNTIFS since it allows for multiple criteria.

    Thanks again!

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    You're welcome!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

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