Sumproduct and Countif On Numbers Only!!

mcget

New Member
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

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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))

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

Thanks

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

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))

Jason, well spotted!!

I feel ashamed i didnt notice that!!!

Works like a charm!

Thanks Dominic.. and Jason!

Paul

Replies
3
Views
45
Replies
1
Views
132
Replies
7
Views
61
Replies
2
Views
344
Replies
2
Views
282

1,207,089
Messages
6,076,517
Members
446,211
Latest member
b306750

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back