SUMIF / SUMPRODUCT multiple criteria

[Hedwardson]

Hello,

I have included the formulae I am using below. The one I am not happy with is the one in bold.
Currently this picks up all items with a Vendor (col O) of 'CASH', but this double counts the codes listed (col H).
I would like help with a formula to look at the 'CASH' (col O) and EXCLUDE any of the accounts listed (col H).

Thanks in advance.
Hayley
[TABLE="width: 680"]
<tbody>[TR]
[TD]Total Accruals Report
[/TD]
[TD]=SUM(NS5_Data!$N$2:$N$6000)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A/cs not to accrue
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]330
[/TD]
[TD]=SUMPRODUCT((LEFT(NS5_Data!$H$2:$H$6000,3)=B5&"")*NS5_Data!$N$2:$N$6000)
[/TD]
[/TR]
[TR]
[TD]430
[/TD]
[TD]=SUMPRODUCT((LEFT(NS5_Data!$H$2:$H$6000,3)=B6&"")*NS5_Data!$N$2:$N$6000)
[/TD]
[/TR]
[TR]
[TD]431010
[/TD]
[TD]=SUMIF(NS5_Data!$H$2:$H$6000,B7,NS5_Data!$N$2:$N$6000)
[/TD]
[/TR]
[TR]
[TD]431020
[/TD]
[TD]=SUMIF(NS5_Data!$H$2:$H$6000,B8,NS5_Data!$N$2:$N$6000)
[/TD]
[/TR]
[TR]
[TD]431030
[/TD]
[TD]=SUMIF(NS5_Data!$H$2:$H$6000,B9,NS5_Data!$N$2:$N$6000)
[/TD]
[/TR]
[TR]
[TD]CASH
[/TD]
[TD]=SUMIF(NS5_Data!$O$2:$O$6000,B10,NS5_Data!$N$2:$N$6000)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Accruals to Post
[/TD]
[TD]=+C2-SUM(C5:C10)
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT(ISNA(MATCH(LEFT(NS5_Data!$H$2:$H$6000,3),B5:B10&"",0))*ISNA(MATCH(NS5_Data!$H$2:$H$6000&"",B5:B10&"",0)),--(NS5_Data!$O$2:$O$6000=B10),NS5_Data!$N$2:$N$6000)

M.

 

[Hedwardson]

Perfect.
Thank you so much.
Hayley

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.