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]

Maybe...

Question 1
=SUMPRODUCT(N(+A2:D4)*(C2:F4="PAID"))

Question 2
=SUMPRODUCT(N(+A2:D4)*(C2:F4="CANCEL"))

Hope this helps

M.

 

[helpexce]

Maybe...

Question 1
=SUMPRODUCT(N(+A2:D4)*(C2:F4="PAID"))

Question 2
=SUMPRODUCT(N(+A2:D4)*(C2:F4="CANCEL"))

Hope this helps

M.

The formula is awesome. Can you please explain the "+" sign before the range inside the brackets?

 

[Marcelo Branco]

The formula is awesome. Can you please explain the "+" sign before the range inside the brackets?

The + sign forces N to return an array of values. Without it N returns just the first value.

M.

 

[Wobzy]

Maybe...

Question 1
=SUMPRODUCT(N(+A2:D4)*(C2:F4="PAID"))

Question 2
=SUMPRODUCT(N(+A2:D4)*(C2:F4="CANCEL"))

Hope this helps

M.

Brilliant, thanks buddy.

But for my purpose example 1 is <>"CANCEL")) where example 2 is ="CANCEL"))

Much appreciated.

 

[Wobzy]

Maybe...

Question 1
=SUMPRODUCT(N(+A2:D4)*(C2:F4="PAID"))

Question 2
=SUMPRODUCT(N(+A2:D4)*(C2:F4="CANCEL"))

Hope this helps

M.

I just realized regarding the first example, using <>"PAID")) isnt right for my particular data set.

I've tried using ="DATA1","DATA2")) without success.
I've tried using ,{"DATA1","DATA2"})) without success.
I've also tried using =NAMED_RANGE)) as the data set is part of a data list in a named range, also didn't work.

In this formula how would i go about specifying two criteria using wild cards, or the named range of 50+ cells?

Thank you.


EDIT; all sorted never mind thank you i made it work with the named range.
Actually no i didnt, help still required plz.

=(

 

[Marcelo Branco]

Try to provide a data sample for which the formula is not working along with expected result(s).

M.

 

[Wobzy]

Try to provide a data sample for which the formula is not working along with expected result(s).

M.

Hi marcelo,

It works great, but i failed to realize SUMPRODUCT isnt going to work with wildcards.

After much googling I gather i need to implement (ISNUMBER(SEARCH but im struggling.

In the example above given that it works, could you show me how to complete the formula using either;
- multiple search criteria (wildcarded) or
- range or named range

Thanks so much again.

 

[Excelislife]

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.

 

[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​

Name 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.