+ Reply to Thread
Results 1 to 5 of 5

Using SUMIFS, how to create criteria to include if cell doesn't match specific data

  1. #1
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    180

    Using SUMIFS, how to create criteria to include if cell doesn't match specific data

    HELP....I am trying to use a SUMIFS formula. Basically, I am trying to have each column sum data from the second sheet column J or K depending on specific criteria.

    Here we go:
    Sheet 1, want column E to sum data from sheet 2 column J based on sheet 2 column G, H, I matching or in this case column I not matching certain Data. So if you focus on sheet one rows 3-5. I want the expended amount to reflect the sum of the expended amount on sheet two as long as the account # on sheet 2 matches D1, the account code on sheet 2 matches B3 (for these three rows) and the program code matches either C3 or C4 or for row 5 doesn't match C3 (000000) or C4 (601000).

    Any suggestions?
    12-13 budget-test.xlsx

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Using SUMIFS, how to create criteria to include if cell doesn't match specific data

    Hi Dena,

    What is the issue you are facing with SumIF function?
    What should be the answer in which cell and the basis what criteria?

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    180

    Re: Using SUMIFS, how to create criteria to include if cell doesn't match specific data

    DILIPandey,

    I am currently using =SUMIFS('NSD Orders'!$J:$J,'NSD Orders'!$G:$G,D$1,'NSD Orders'!$H:$H,$B3,'NSD Orders'!$I:$I,"<>000000",'NSD Orders'!$I:$I,"<>601000") on sheet 1 row 5 column E. If I test the formula by entering the data I have on sheet 2, it pulls in the data properly on row 3 column e when I enter the program code as 010200 but when I change the program code on sheet 2 from 010200 to 000000 the data in row 5 column E doesn't disappear but the data appears correctly in column e row 3. Why isn't the formula working properly?

    Any help would be fabulous.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Using SUMIFS, how to create criteria to include if cell doesn't match specific data

    Row 3 and Row 5 have different formulas.... what should be the logic to get the values.. and also I am not able to find sheet 1, sheet2.. though I am considering them from left to right..so overview sheet becomes sheet 1.. please explain.. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    180

    Re: Using SUMIFS, how to create criteria to include if cell doesn't match specific data

    What I was trying to do is for column B anytime it states "OTHER" that when I enter the program code in on the NSD ORDERS tab, that if its not 601000 or 000000 then the expended or transfer amounts show on the row with OTHER vs the rows with 601000 or 000000. The overview sheet should be sheet 1

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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