I have a table in Excel called TMarks.

It includes a column called AdjustedMark, which contains values between zero and 100.

Sometimes the data in this table may be filtered. I want to count the number of rows where the AdjustedMark is greater than zero, ignoring hidden rows. I have created the following formula:

=AGGREGATE(3, 7, TMarks[AdjustedMark]/(TMarks[AdjustedMark]<>0))

However, this formula is returning a #Value error, and I don't understand why. I have also tried this as an alternative:

=AGGREGATE(9, 5, (TMarks[AdjustedMark]>0)*1)

But this also returns #VALUE!

All the rows in the AdjustedMark column of the table are numeric.

I have a table in Excel called TMarks.

It includes a column called AdjustedMark, which contains values between zero and 100.

Sometimes the data in this table may be filtered. I want to count the number of rows where the AdjustedMark is greater than zero, ignoring hidden rows. I have created the following formula:

=AGGREGATE(3, 7, TMarks[AdjustedMark]/(TMarks[AdjustedMark]<>0))

However, this formula is returning a #Value error, and I don't understand why. I have also tried this as an alternative:

=AGGREGATE(9, 5, (TMarks[AdjustedMark]>0)*1)

But this also returns #VALUE!

All the rows in the AdjustedMark column of the table are numeric.

• excel
• excel-formula

1 Answer 1

AGGREGATE does not allow ARRAYS in the third criterion for types with a numerical value less than 14. For 3 and 9 the area must be ranges.

Instead use COUNT(FILTER())

=COUNT(FILTER(TMarks[AdjustedMark],(TMarks[AdjustedMark]>0)*(SCAN(0,TMarks[AdjustedMark],LAMBDA(z,y,SUBTOTAL(3,y))))))

The SCAN() will return an array of 1s and 0s. 1 for visible and 0 for hidden, row by row. Then by multiplying this with the secondary check for 0 value we get an array of rows that match both. Then Filter returns those values and they are counted.