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

[Dena]

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

[dilipandey]

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>

[Dena]

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.

[dilipandey]

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>

[Dena]

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