0

I have this formula:

=OtherSheet!$B$2*COUNTIF(C6:CX6;OtherSheet!$A$2)

It looks in the columns to the right and for each occurrence of a string from the other sheet's A2 it adds the other sheet's B2.

I have multiple rows in the other sheet, so A3, A4 and so on contain other strings, and B3, B4... contain other values. Right now, I'd copy the original formula to give

=OtherSheet!$B$2*COUNTIF(C6:CX6;OtherSheet!$A$2)+OtherSheet!$B$3*COUNTIF(C6:CX6;OtherSheet!$A$3)OtherSheet!$B$4*COUNTIF(C6:CX6;OtherSheet!$A$4)

This is rather annoying, obviously, if there are lots of rows on the other sheet. What I'm looking for is a way to tell Excel to look in all the rows of the other sheet in an expression that doesn't grow when the number of rows increases.

Something like this, but valid excel:

=SUM_OVER_ALL_ROWS_OF_THE_OTHER_SHEET( OtherSheet!$B$ROW*COUNTIF(C6:CX6;OtherSheet!$A$ROW) )
Improve this question

1 Answer 1

Reset to default
0

Excel obeys the rules of basic mathematical hierarchy and combined operations.

'this...
=1*99+2*99+3*99+4*99
'is the same as this,
=(1+2+3+4)*99
'or even this,
=sum(1:4)*99

You can find the last true number in a column by looking for an impossibly large number.

=index(b:b, match(1e99, b:b))

Your formula would be,

=sum(b2:index(b:b, match(1e99, b:b)))*countif(c6:cx6, a2)

You can add the name of the super-secret worksheet where appropriate.

Improve this answer
2
  • i don't have all 99s, the countifs are all different
    – Haukinger
    Apr 3, 2020 at 15:04
  • I'm very sorry that my response did not immediately resolve your problem .
    – user385793
    Apr 3, 2020 at 15:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .