Excel Sum Product help

[calcharn]

Morning

 

I have this formula below working on a spreadsheet. Its that old I cant even remember how i created it. But it works for what i need.

 

I just want to manipulate it to count numbers between 20 and 39, but no matter what I try I cant get it to work.

 

Can anyone help?

 

=SUMPRODUCT(SUBTOTAL(3,OFFSET(AI3:AI875,ROW(AI3:AI875)-MIN(ROW(AI3:AI875)),,1))*(AI3:AI875>=20))

[Dos_Box]

Moving to the correct forum.

[mavhc]

Morning

 

I have this formula below working on a spreadsheet. Its that old I cant even remember how i created it. But it works for what i need.

 

I just want to manipulate it to count numbers between 20 and 39, but no matter what I try I cant get it to work.

 

Can anyone help?

 

=SUMPRODUCT(SUBTOTAL(3,OFFSET(AI3:AI875,ROW(AI3:AI875)-MIN(ROW(AI3:AI875)),,1))*(AI3:AI875>=20))

 

sumproduct is a hack to count only certain rows, here where ai3:ai875 > 20

 

subtotal(3 does subtotal for counta, which counts non blank cells

 

and Offset is counting, from ai3:ai875, the current row minus the first row, and 1 wide

 

What is it supposed to do?

[howartp]

I think an anonymised sample sheet and what you're trying to achieve is going to be needed here - that's quite a formula!

[andy_b]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(AI3:AI875,ROW(AI3:AI 875)-MIN(ROW(AI3:AI875)),,1))*(AI3:AI875>=20)*(AI3:AI875<=39))

 

?

[Ditto]

It's probably just a typo, but both the OP and last suggestion has "AI 875" in the first ROW() function. You'll need to remove that space. But as suggested, an anonymised sheet would be good to see if there are more concise solution.

 

I have had to grapple with SUMPRODUCT, SUBTOTAL, OFFSET and more recently. Unlike pivot table data, which works well with filtered data, getting other functions to work with filtered datasets can be a right pain. MS should come up with a better function that can work on filtered datasets with less faff!

[mavhc]

subtotal has options for not including hidden cells, does that work for filtered cells?

[Ditto]

I can't recall if rows filtered out are regarded as hidden, but I think it must do. But the approach still ends up with a complex formula - for example a simple COUNTIFS with a few conditions against a table, is relatively simple. But if you filter values on the table, it just does the calculation on the whole table, so you end up with the complex SUMPRODUCT, SUBTOTAL, OFFSET, MIN and ROW stuff. If there were a parallel set of functions that just worked on the filtered data, that would be great. So, COUNTIFS has a parallel FCOUNTIFS etc. for a number of other functions.

 

For those interest in the joys of SUBTOTAL - see https://support.microsoft.com/en-gb/office/subtotal-function-7b027003-f060-4ade-9040-e478765b9939

Edited October 2, 2024 by Ditto

[Ditto]

https://www.excelforum.com/excel-formulas-and-functions/1203384-sumproduct-w-offset-to-create-a-rolling-average-for-filtered-cells.html - this thread discussion demonstrates the challenge - there are better explanations out on interweb, but I don't have them to hand.