Sumif Not Equal To

[BMil8]

I feel like I'm going crazy. Why does this formula not work? I'm just trying to sum the values in row 2 not equal to the current month. These months are stored as text and are not actual dates.

=SUMIF(B1:J1,"<>"&H1,B2:J2)

I would expect this to equal 17,556 but it returns to full 28,248.

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

Month	Nov-21	Nov-21	Nov-21	Nov-21	Dec-21	Dec-21	Dec-21	Dec-21	Dec-21
Units	1344	5604	7236	3372	2820	4548	3324	0	0

 

[kweaver]

continents.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Month	21-Nov	21-Nov	21-Nov	21-Nov	21-Dec	21-Dec	21-Dec	21-Dec	21-Dec
2	Units	1344	5604	7236	3372	2820	4548	3324	0	0	17556
Sheet9

Cell Formulas
Range	Formula
K2	K2	=SUMIF(B1:J1,"<>"&H1,B2:J2)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Joe4]

Your formula works fine for me (and I copied and pasted your data directly to my sheet).
It returns 17556.

Make sure that your ranges are accurate, and then you do not have any extra spaces in your values in row 1.

 

[Fluff]

The formula doesn't work when the headers are text, rather than dates. As (I think) the xxifs functions will convert them.

 

[BMil8]

The formula doesn't work when the headers are text, rather than dates. As (I think) the xxifs functions will convert them.

Is there a something I can add to the formula to help it work as text?

 

[Fluff]

Thanks for updating your profile.
You could use

=SUM(FILTER(B2:J2,B1:J1<>H1))

 

[Joe4]

The formula doesn't work when the headers are text, rather than dates. As (I think) the xxifs functions will convert them.

Ah, interesting...
Yes, I see when I copied it over, it pasted the headers as dates instead of text, which explains why it was working for me.

 

[Joe4]

A SUMPRODUCT formula also appears to work here:

=SUMPRODUCT(--(B1:J1<>H1),B2:J2)

 

[BMil8]

Thanks to both of you!