Results 1 to 4 of 4

Thread: #VALUE error with Sumifs formula

  1. #1
    Board Regular willow1985's Avatar
    Join Date
    Jul 2019
    Location
    Winnipeg, MB
    Posts
    218
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default #VALUE error with Sumifs formula

    Hello,

    I do not know what is wrong with the below formula - receiving a #Value error:

    =SUMIFS('WO Report'!F2:F1000000, $B$14,'WO Report'!V2:V1000000,$B$12,'WO Report'!S2:S1000000)

    Let me break the information down for you, I have 2 tabs: Tables and WO Report. The formula is in the tab called Tables.

    WO Report Tables
    Column F Column V Column S Cell B12 Current Year
    Invoice amount Month Year
    250 September 2019 Cell B14 Current Month
    250 August 2019
    50 September 2019 Formula
    10 August 2018

    Column F in WO Report is invoice amounts
    Cell B14 is the current month name, aka: September - (I have it reference like this so we can modify the month we want to look at)
    Column V in the WO Report has a formula to determine the Month name
    Cell B12 is the current year
    Column S in the WO Report has a formula that calculates the current year for the data on that sheet.

    What I am looking to do is SUM all of the invoice amounts in Column F if the Month and year matches what is listed in B12 & B14 on the Tables tab. No idea why I am getting a #Value error.

    Note: The invoice amount and month columns could have blanks

    Any help would be greatly appreciated

    Thank you

    Carla
    Last edited by willow1985; Sep 27th, 2019 at 05:00 PM.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,888
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    15 Thread(s)

    Default Re: #VALUE error with Sumifs formula

    The ranges and criteria are reversed, try this


    =SUMIFS('WO Report'!F2:F1000000, 'WO Report'!V2:V1000000,$B$14, 'WO Report'!S2:S1000000, $B$12)
    Regards Dante Amor

  3. #3
    Board Regular willow1985's Avatar
    Join Date
    Jul 2019
    Location
    Winnipeg, MB
    Posts
    218
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #VALUE error with Sumifs formula

    I must be tired. Thank you very much!

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,888
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    15 Thread(s)

    Default Re: #VALUE error with Sumifs formula

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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