Count + Sumproduct question.

[IanMackayinAmsterdam]

Hi there,

I have a sheet where I want to calculate the count of 1 column (which values are 10 digit material numbers) based on the country in another column but only if a third column's value is greater than 1.

So column B = country column C = material number and column J = value check if greater than 1.

Here is an example:

[B]Column B             Column C       Column J [/B]
Belgium              1234567891       100
Belgium              1234567893       100
Belgium              1234567894
Netherlands          1234567895       300
Spain                1234567895       50

So in my formula the result for Belgium would be 2 as there are 2 material numbers in column C which have a value in column J that are greater than 1.

I hope this is clear, I assume I need to use a SUMPRODUCT formula with a countif nested in there somewhere but I've tried and can't seem to get it to work.

Any help would be greatly appreciated.

Thanks in advance,

Ian.

 

[njimack]

=SUMPRODUCT(--($B$2:$B$6="Belgium"),--($J$2:$J$6>1))

 

[Jonmo1]

You don't need countif in there, just sumproduct by itself works.

Try

=SUMPRODUCT(--(B1:B100="Belgium"),--(ISNUMBER(C1:C100)),--(J1:J100>1))


Hope that helps.