# Question on using SUMIFS + AND statement

#### darbebo

##### New Member
Hi guys,

this may be a weird one but I am trying to do a sumifs formula with multiple criteria, and one of the criteria requires that the value in column E and column C do NOT equals to the respective cells in page 1 at the same time.

This below may be the confusing so let me know if i can explain more.

Trying to :
add up exposures for column H (page 2) looking at column G (page 2) as criteria rage

criteria #1 : matches cell M8 (page1)
Criteria #2: column D (page 2) matches cell K8 (page 1)
criteria #3 column E(page2) does NOT equal to cell J8 on page 1 AND AT THE SAME TIME column C(page2) does not equal to cell I8 on page 1

=SUMIFS('Page2'!H:H,'Page2'!G:G,'Page1'!M8,'Page2'!D:D,'Page1'!K8,AND('Page2'!E:E,"<>'Page1'!"&J8,'Page2'!C:C,"<>'Page1'"&I8))

I have no idea how to fit this and statement in, because right now it's not working correctly because without the And-statement it will not sum the value if EITHER column E and column C match.

I do NOT want it to sum if BOTH column C and Column E (page 2) matches the respective cells on page 1. But if EITHER one does not match i WANT it to sum.

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### FDibbins

##### Well-known Member
Try this...
=SUMIFS('Page2'!H:H,'Page2'!G:G,'Page1'!\$M\$8,'Page2'!D:D,'Page1'!\$K\$8,'Page2'!E:E,"<>"&'Page1'!"\$J\$8,'Page2'!C:C,"<>"&'Page1'\$I8)

As long as the criteria are in different columns, you can just keep adding them to the SUMIFS

#### darbebo

##### New Member
Hi there thanks for the quick reply. This was actually my initial attempt (as it was the easiest thing to do by default). but by doing that it will NOT sum if either column E or C matches their respective cells. I need it to sum if either one does not match (meaning if BOTH match, then do NOT sum).

But right now it's not summing it even if only 1 matched

##### MrExcel MVP
Control+shift+enter, npt just enter:
Rich (BB code):
``````=SUM(
IF(ISNUMBER(MATCH('Page 2'!\$C\$2:\$C\$900,'Page 1'!I8:J8,0))+ISNUMBER(MATCH('Page 2'!\$E\$2:\$E\$900,'Page 1'!I8:J8,0)),
IF(1-('Page 2'!\$C\$2:\$C\$900='Page 2'!\$E\$2:\$E\$9),
IF('Page 2'!\$G\$2:\$G900='Page 1'!M8,
IF('Page 2'!\$D\$2:\$D\$900='Page 1'!K8,
'Page 2'!\$H\$2:\$H\$900)))))
``````

#### darbebo

##### New Member
Thanks so much all!!

Replies
10
Views
980
Replies
1
Views
441
Replies
1
Views
451
Replies
4
Views
283
Replies
2
Views
370

1,191,501
Messages
5,986,924
Members
440,067
Latest member
Swatts1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back