+ Reply to Thread
Results 1 to 3 of 3

Sumif issue how to include two columns in a different sheet.

  1. #1
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    317

    Sumif issue how to include two columns in a different sheet.

    Hi Guys
    Firstly all the data in the attached excel file is fictitious. I mention this as some contentious
    Excel forums colleagues were concerned that I was baring my lot and suggested I make this point clear. Thank you and I will in future.
    I have a sumif which refers to columns in another sheet and returns totals within a date range (VAT). My problem is when I alter that to take in two columns I get a #Values return. The columns I ma trying to get the data from are fine. I created a simple sum in the adjacent column and got the answer. Of course Excel can do what I want but not in the way I am asking her to.
    Below the two functions
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    The tab/sheet I am using is VAT I have highlighted the cells. you may see from the above where I am going wrong. I will be copying it to the others and then incorporating the answer into a macro.

    Mark
    Attached Files Attached Files

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Sumif issue how to include two columns in a different sheet.

    your ranges must be the same size-if the data range is two columns so must the criteria ranges be. try sumproduct instead
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    317

    Re: Sumif issue how to include two columns in a different sheet.

    Hi Joseph P

    They are. but I have now learnt that they must be the same size.

    Tried Sumproduct and got the same result. Which got me to thinking.

    I then altered $X$2:$Y$883 to $Y$2:$Y$883 and also to $Z$2:$Z$883 ie the two component columns separately and got the answer on both occasions. But one looked incorrect.

    In BDC2 when I sum the column to row 883 I get 2608.46. When I use the function as in the above I get 624.21. I created a continuous cash flow type column and saw where the #value was occurring. The #Value starts when I have "-". This is the result of my earlier function. It would seem that sum can work round "-" and add up the column but the sumifs function above cannot. I had used "-" just for visual purposes. I have replaced it with "0". Now all is well. It would be unfair of me to tangent off to ask why this is.
    For your information here are the two functions.

    Please Login or Register  to view this content.
    If the result of the function is the “-“ then the sumif does not work.
    So I have changed it to

    Please Login or Register  to view this content.
    Now all the roses in the garden have a beautiful perfume.
    Thanks
    Mark

+ 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. Sumif with cells that include a subset of text in any position
    By mcvittsa in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-08-2013, 12:22 PM
  2. How do I include a SUMIF in a IF(AND statement?
    By johnw993 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-08-2012, 01:12 AM
  3. SUMIF to include several conditions?
    By tangcla in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-28-2008, 08:39 PM
  4. Excel chart issue - cannot include a cell in my data series
    By Russell-stanely in forum Excel General
    Replies: 0
    Last Post: 04-21-2006, 12:35 PM
  5. Include Subtotal in SumIf
    By Bonobo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-27-2005, 08:05 AM

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