Question on using SUMIFS + AND statement

[darbebo]

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.

thanks in advance

 

[FDibbins]

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]

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

 

[Aladin Akyurek]

Control+shift+enter, npt just enter:

=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]

Thanks so much all!!

 

[Aladin Akyurek]

Thanks so much all!!

Glad to help...