Summary of data

[Soleblue]

Good morning guys ,
I wanted to know from you if it is possible from columns U and V to make a "summary" of the data that is not at 0 or #N/A in columns AE and AF with a formula.

I was using in H2
=IF.ERROR(@INDEX(T$2:T$400;AGGREGATE(15;6;(ROW REF($T$2:$T$400)-ROW REF($T$2)+1)/($U$2 :$U$400<>"");ROWS(T1:$T$1)));"")
but it doesn't work for me

Thank you in advance
Sole

 

[Peter Bartholomew]

I assume you are using a legacy version of Excel. Otherwise you could use FILTER.

 

[Soleblue]

Thanks Peter,
Yes, sure, I use 365 with contract.
But I don't understand the formula, I can't translate it into Italian.
You're talking about the "filter" formula, right?
No normal Excel filters. I didn't know this formula.

 

[Peter Bartholomew]

I guess Il 'foglio' was the clue

Let me know how the formula translates.

 

[Soleblue]

Hello Peter,
the formula in Italian would be this
= FILTRO(table; product1 * VAL.NUMERO(product2))
but did you change the simple format with the table format?
I can't use tables, it's a very complicated file where I can't make changes.
What I want to do is a simple change but I can't use tables
I'm so sorry

 

[Peter Bartholomew]

No. The 'table' is simply a defined name applied to the range $T$2:$V$24. To my mind, using Ctrl/T to convert the range to an Excel Table would have improved the formula by making it respond dynamically to new data. However is is better for you as it stands! You could even revert to the direct cell reference though I haven't used one of those since 2015!

 

[Soleblue]

I'm very sorry Peter, I can't adapt it.
I think I need another formula.
Thank you very much for your time wasted for me

 

[bosco_yip]

Try to change your formula:

From this >>

=IF.ERROR(@INDEX(T$2:T$400;AGGREGATE(15;6;(ROW REF($T$2:$T$400)-ROW REF($T$2)+1)/($U$2 :$U$400<>"");ROWS(T1:$T$1)));"")

Into this >>

=IF.ERROR(@INDEX(T$2:T$400;AGGREGATE(15;6;(ROW REF($T$2:$T$400)-ROW REF($T$2)+1)/(($U$2:$U$400>0)+ISNUMBER($V$2:$V$400));ROWS(T1:$T$1)));"")

Regards

 

[p45cal]

I took the values in columns AH:AJ to be expected results so used

=FILTER(T2:V24,((U2:U24<>0)+NOT(ISERROR(V2:V24)))>0,"No results")

or a variation if zeroes can appear n column V or #N/As in column V
See attached.