+ Reply to Thread
Results 1 to 4 of 4

SUMIFS Problem - A value used in the formula is of the wrong data type

  1. #1
    Registered User
    Join Date
    01-09-2014
    Location
    United Kingdom
    MS-Off Ver
    Mac Excel 2011
    Posts
    20

    SUMIFS Problem - A value used in the formula is of the wrong data type

    Hi,

    I have a spready with 3 sheets, expenses, outgoings, incomings

    Each sheet contains a few named ranges

    Expenses:

    - ExpenseAmount
    - ExpenseMonth
    - ExpenseYear
    - ExpenseType

    Outgoings:

    - OutgoingAmount
    - OutgoingType

    etc


    In the expenses sheet I've created a formula


    =SUMIFS(ExpenseAmount, ExpenseType, $K11, ExpenseMonth,MONTH(L$3), ExpenseYear, YEAR(L$3))


    This sums the ExpenseAmount based on matching ExpenseType, ExpenseMonth, ExpenseYear and this is working fine.

    However, when I do the same in

    Outgoings
    Incomings

    with separate named ranges, I get an #VALUE error


    Any idea why that is?

    I've attached a sample spready.

    thanks,

    TT
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUMIFS Problem - A value used in the formula is of the wrong data type

    Hi,

    SUMIFS requires that the ranges passed to it be of equal size. Your final Named Range in the formulas in the Outgoings sheet - OutgoingYear - is:

    =Outgoings!$D$5:$D$466

    though the others appear to only extend to row 367.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    01-09-2014
    Location
    United Kingdom
    MS-Off Ver
    Mac Excel 2011
    Posts
    20

    Re: SUMIFS Problem - A value used in the formula is of the wrong data type

    Perfect, that was it! Many thanks!

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUMIFS Problem - A value used in the formula is of the wrong data type

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA replacing a formula with a macro returns #Value (Wrong data type)
    By silent3486 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-14-2013, 04:17 PM
  2. Replies: 4
    Last Post: 04-01-2013, 08:03 AM
  3. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 AM
  4. Replies: 4
    Last Post: 04-20-2012, 08:44 AM
  5. Formula returns wrong data type
    By mbrown89 in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 01-30-2010, 12:36 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1