sumif

[TiaXL]

Hi All,

Appreciate some help if possible.

Is it possible to do a sumif on a <>value ? I have a bit list of order amounts and I want to add them up but I want to exclude a certain product name

 

[VoG]

Try like this

=SUMIF(A1:A3,"<>b",B1:B3)

 

[TiaXL]

Its treated as a valid fromula but doesnt actualy exclude the value so doesnt work

my formula looks like =SUMIF(A:A,"<>U9",M:M) I get a result of 66868.5 doing just sum(M:M) gives the same value

 

[VoG]

Assuming that U9 is a cell reference try

=SUMIF(A:A,"<>"&U9,M:M)

 

[TiaXL]

perfect thanks a lot!

 

[Trevor G]

Try this as a sample.

Excel Workbook
	A	B	C	D	E
1	Product Name	Value		Exclude Product	Total Value
2	Gold	23		Silver	11630
3	Silver	34
4	Bronze	567
5	Gold	752
6	Silver	1024
7	Bronze	1296
8	Gold	1568
9	Silver	1840
10	Bronze	2112
11	Gold	2384
12	Silver	2656
13	Bronze	2928
Sheet2

 

[TiaXL]

that would work too bu the problem is I need to do it on a cell reference as the value will change

Its to create a data set to plot on a graph.

 

[Trevor G]

Then you have a solution with Peters option.

Good to read it will work for you

 

[TiaXL]

now my next question. I am trying to combin my new sumif formula with an IF to check another issue my formula looks like this

IF(S:S=W4,SUMIF(A:A,"<>"&$U$9,M:M),"")

I have a feeling I know why it doesnt work (=IF(S:S=W4) I think is the issue, but I am unsure how to acomplish what I need.

I am now using the same formula from this thread but also trying to limit it to month, I have a column with month numbers in and a cell for each month (columnW containing the relevant month reference number)

The formula works but it is ignoring the first IF and just summing up everything, little help please

 

[VoG]

Try

=SUMPRODUCT(--(S1:S100=W4),--(A1:A100<>U9),M1:M100)