SUM of a column if 2 criteria are met

[veniw]

Hi,

I have an excel sheet with column information such as date (J), # of pcs (K), status (N).

What i want is a formula that sums the K, if J date is within 2014 and N is "xx". I tried working with SUMIF and SOMPRODUCT, but get stuck in either a formula that results in a 0 or some errorr (im aware of ctrl shift enter).

I have this one that works to sum K if J=2014. But how do i get the last IF in there?
=SOMPRODUCT((YEAR(J2:J2000)=2014)*(K2:K2000))

[Richard Buttrey]

Hi,

Use SUMIFS() not SUMIF()

[sweep]

Hi,

Does this work for you?

SOMPRODUCT((YEAR(J2:J2000)=2014)*(N2:N2000="xx")*(K2:K2000))

[veniw]

Thanks, I got now
=SUMIFS(K:K;J:J;YEAR(2014);N:N;"xx")

but that returns 0? If i try this with another column instead of J it works, so I think im doing something wrong with the "year" function?

[veniw]

yes Sweep that works! So my mistake was to make a new matrix of it instead of combining it with the * in the same one.

Though im still curious what was wrong with my previous SUMIFS formula

[sweep]

Whilst I'm happy to be corrected, I don't think it's possible to use another function within the SUMIFS criteria.

[Richard Buttrey]

Thanks, I got now
=SUMIFS(K:K;J:J;YEAR(2014);N:N;"xx")

but that returns 0? If i try this with another column instead of J it works, so I think im doing something wrong with the "year" function?

It depends what column J contains. If dates then YEAR(2014) won't work since that will return the value 1905 being 5 years (2014/365) after 1900.
You will need to use two date criteria, i.e

SUMIFS(K:K;J:J;">="&41640;J:J;"<="&42004;N:N;"xx")

[veniw]

i see, thanks. For the SUMPRODUCT year 2014 works fine, how come? Why is that formula "able" to read YEAR(2014) properly?

[FDibbins]

Whilst I'm happy to be corrected, I don't think it's possible to use another function within the SUMIFS criteria.

You can use (pretty much) anything to create the criteria. YEAR() should work just fine