Results 1 to 3 of 3

Thread: SUMIFS formula with the use of "*" in the criteria and separate use of ISNUMBER

  1. #1
    Seeker jaadu's Avatar
    Join Date
    Nov 2011
    Excel Version
    Excel 2010

    SUMIFS formula with the use of "*" in the criteria and separate use of ISNUMBER

    Register for a FREE account, and/
    or Log in to avoid these ads!


    Referring to the attached excel file (Excel 2010 version), I have 2 questions regarding formulas for some data I am reviewing.

    1. SUMIFS formula with the use of "*" in the criteria. I can get this to work with 2 separate SUMIF formulas, but it does not work in one SUMIFS formula.

    2. Use of ISNUMBER. The formula listed below is from cell D3 in the attached file. Basically, column C has #s, but in case I enter a letter or word in column C, I want column D to to tell me to "Check Amount", instead of Credit or Debit. I believe ISNUMBER could do the trick, but I could not get it to work:

    =IF(C2<0,"CREDIT",IF(C2>=0,"DEBIT","Check Amount"))
    If you can help me out, I would greatly appreciate it.
    Attached Files Attached Files

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Brussels Belgium
    Excel Version
    2010 on Xubuntu
    Question 1 :As you are using a OR logic, you cannot use SUMIFS which is an AND logicExcel questions for SUMIFS and ISNUMBER.xlsx
    Thank you Ken for this secure forum.

  3. #3
    Acolyte gue's Avatar
    Join Date
    Nov 2018
    Excel Version
    Office Professional Plus 2016
    in your formula
    HTML Code:
    IF(C2<0,"CREDIT",IF(C2>=0,"DEBIT","Check Amount"))
    the TRUE-part is "CREDIT" and the FLASE-part (already" >=0") can only get the "DEBIT" and can never become FALSE!

    the correct formula is (first checking if the input is valid):
    =IF(ISNUMBER(C2);IF(C2<0;"CREDIT";"DEBIT");"Check Amount")

    The easiest way to get the sum of both is the formula "=B60+B61" in your excel file.
    Attached Files Attached Files

Posting Permissions

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