SUMIF criteria met or not met.

[Wobzy]

Hi Guys,

	A	B	C	D	E	F
1	MERGED_A1	MERGED_A1	MERGED_A1	MERGED_D1	MERGED_D1	MERGED_D1
2	$50	ITEM	PAID	$45	ITEM	CANCEL
3	MERGED_A3	MERGED_A3	MERGED_A3	MERGED_D3	MERGED_D3	MERGED_D3
4	$50	ITEM	PENDING	$50	ITEM	PAID

<tbody>
</tbody>

My working sheet is much larger but this is the jist of it.

My formula needs to SUM A2,D2,A4,D4 only IF C2,F2,C4,F4 are not equal to "CANCEL" or "PENDING".
I also need to SUM the same former range in the instance that the latter range is equal to CANCEL.

Help is appreciated.

Thank you.

 

[Marcelo Branco]

Not sure if i understand what you are looking for

See if this helps

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	MERGED_A1​	MERGED_A1​	MERGED_A1​	MERGED_D1​	MERGED_D1​	MERGED_D1​		Formula​	Exclude​
2​	50​	ITEM​	PAID​	45​	ITEM​	CANCEL​		100​	CANCEL​
3​	MERGED_A3​	MERGED_A3​	MERGED_A3​	MERGED_D3​	MERGED_D3​	MERGED_D3​			PENDING​
4​	50​	ITEM​	PENDING​	50​	ITEM​	PAID​

<tbody>
</tbody>

Named range Exclude = I2:I3 (gray area) contains the keywords that should not be taken in account in calculations.

Formula in H2
=SUMPRODUCT(N(+A2:D4)*ISNA(MATCH(C2:F4,Exclude,0)))

M.

 

[Wobzy]

The problem is you are using garbage data with bad formatting. Remove merged cells, merged cells are a huge headache when analyzing data. Second, put all similar information in the same column. Maybe work on a macro to format this correctly.

Thanks for that long string of useless words, but I hope you mind if I disregard your opinion and go with Marcello's solid ability to provide actual answers to the question being asked.

 

[Wobzy]

Not sure if i understand what you are looking for

See if this helps

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	MERGED_A1​	MERGED_A1​	MERGED_A1​	MERGED_D1​	MERGED_D1​	MERGED_D1​		Formula​	Exclude​
2​	50​	ITEM​	PAID​	45​	ITEM​	CANCEL​		100​	CANCEL​
3​	MERGED_A3​	MERGED_A3​	MERGED_A3​	MERGED_D3​	MERGED_D3​	MERGED_D3​			PENDING​
4​	50​	ITEM​	PENDING​	50​	ITEM​	PAID​

<tbody>
</tbody>

Named range Exclude = I2:I3 (gray area) contains the keywords that should not be taken in account in calculations.

Formula in H2
=SUMPRODUCT(N(+A2:D4)*ISNA(MATCH(C2:F4,Exclude,0)))

M.

You're an absolute champion mate.