Help with sumproduct

[kasbac]

Hi there

I have runned into some problem with a sumproduct formula that im trying to write so far i have gotten this far:

=SUMPRODUCT(Compilation!A2:A4000;--(Compilation!K2:K4000=1);--(Compilation!P2:P4000="XXX");--(Compilation!Z2:Z4000="ZZZ")

The next condition that I would like to add is that if the data in colloum Compilation!N2:N4000 equals either withdrawn, cancelled or rejected, these lines should NOT be included in the sum.

I have had some help with a similar problem in this thread http://www.mrexcel.com/forum/showthread.php?t=323786 but simply cant seem to get it working in the new formula

Hope for some help if any clarification is needed please let me know

thank you

 

[Jonmo1]

This should do it..

=SUMPRODUCT(Compilation!A2:A4000,--(Compilation!K2:K4000=1),--(Compilation!P2:P4000="XXX"),--(Compilation!Z2:Z4000="ZZZ"),--(NOT(ISNUMBER(MATCH(Compilation!N2:N4000,{"Cancelled","Withdrawn","Rejected"},0)))))

HTH

 

[Aladin Akyurek]

=SUMPRODUCT(
    Compilation!A2:A4000;
    1-ISNUMBER(MATCH(Compilation!N2:N4000;List;0));
    --(Compilation!K2:K4000=1);
    --(Compilation!P2:P4000="XXX");
    --(Compilation!Z2:Z4000="ZZZ"))

where List is a 3-cell range housing the items withdrawn, cancelled, and rejected.

Hi there

I have runned into some problem with a sumproduct formula that im trying to write so far i have gotten this far:

=SUMPRODUCT(Compilation!A2:A4000;--(Compilation!K2:K4000=1);--(Compilation!P2:P4000="XXX");--(Compilation!Z2:Z4000="ZZZ")

The next condition that I would like to add is that if the data in colloum Compilation!N2:N4000 equals either withdrawn, cancelled or rejected, these lines should NOT be included in the sum.

I have had some help with a similar problem in this thread http://www.mrexcel.com/forum/showthread.php?t=323786 but simply cant seem to get it working in the new formula

Hope for some help if any clarification is needed please let me know

thank you

 

[Jonmo1]

1-ISNUMBER(MATCH

That's clever,

1 - ISNUMBER

instead of
NOT(ISNUMBER)


I was about to post back to point out that the OP wanted to NOT count those rows....Untill I saw the 1-