Sumproduct and Countif On Numbers Only!!

[mcget]

Hi All,

I want to do a sumproduct on a FILTERED list of numbers, but only count the numbers which are equal to or more than 15.

I use the following formula on a similar set of data, although the values to count in this are text, not numbers;

=SUMPRODUCT(SUBTOTAL(3,OFFSET(I5:I5000,ROW(I5:I5000)-MIN(ROW(I5:I5000)),,1))*(I5:I5000="Active"))

Is there a way to replace "Active" with >14 WITHOUT it summing the resulting numbers, rather than counting?

Any help would be much appreciated!

Thanks

Paul

 

[Domenic]

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(I5:I5000,ROW(I5:I5000)-MIN(ROW(I5:I5000)),,1))*(I5:I5000>14))

However, in this particular case, it can be shortened as follows...

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(I5:I5000,ROW(I5:I5000)-MIN(ROW(I5:I5000)),,1))>14))

 

[mcget]

Unfortunately this has not worked. It comes up with the answer 0 for everything. Any other ideas?

Thanks

 

[mcget]

The 0's were from your second formula, the first formula results in counting the number of values in total, not just the ones that are >14.

Thanks

 

[jasonb75]

Is the formula looking at the correct column?

Domenic has used the ranges from your example formula, as that was for text entries, I'm guessing that the numeric values you're now looking at are ina different column.

Also

MIN(ROW(I5:I5000)) is the same as ROW(I5) so you can shorten it a little bit more

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(I5:I5000,ROW(I5:I5000)-ROW(I5),,1))>14))

 

[mcget]

Jason, well spotted!!

I feel ashamed i didnt notice that!!!

Works like a charm!

Thanks Dominic.. and Jason!

Paul