How to Use CountIfs to Calculate a Running Total.

And to reply specifically your question about the error you get: COUNTIFS requires that all range arguments are the same size.

To start with:

ROW('Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$5:$D$14)

will always returns 5, the first row number of the range D5:D14. Maybe you meant to use ROWS instead, the count of rows, it's really hard to say.

Then D5:D14 is filled with values of different types, so you probably meant to use D5:M5 like in my formulas.

Then to COUNTIFS.

COUNTIFS(

  'Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$5:$D$14, "<>N/A",

Here we have a range where we compare each cell to N/A. So far so good.

  ROW('Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$5:$D$14) <= ROW('Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$5),

  ROW('Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$5:$D$14) - ROW('Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$5) + 1

)

Here we have a boolean introduced by the <=, compared to a numerical value.

So the error message tells you that the range arguments are not the same size, obviously D5:D14 compared to a single boolean value.

Maybe you meant as the second test

ROW('Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$5:$D$14), "<="&ROW('Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$5)

but then what happens to the last part? Frankly I cannot understand where you were going with that.

Look at the following and meditate on it.

I think your whole monster formula could be simplified to:

=AVERAGEIF('Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$5:$M$5,"<>N/A",'Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$8:$M$8)

Maybe little tweak is necessary because I don't know where is I4.

Taking into account N/A and empty cells, the formula becomes:

=IFS('Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$5="N/A","",


'Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$5="",0,


COUNTMATCHES("LC,SC,LP,SP,LA,SA",'Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$5)>0,
 AVERAGEIFS('Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$8:$M$8,


'Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$5:$M$5,"<>N/A",


'Enter up to 10x Legs/Strategy - Tied to 10x BSM Banks'::$D$5:$M$5,"<>"))

On rows 2 to 4 and 7 to 9 the yellow cells are those that return a TRUE result to the adjacent one-condition COUNTIFS. Column Result shows their count.

Rows 5 and 10 have no values in Test-values because the combined COUNTIFS actually uses the three rows of Test-values above for three separate tests. Result is the count of yellow cells resulting from the combined COUNTIFS as opposed to the individual ones on previous rows.

It's a visual aid to understand what a multi-conditions COUNTIFS does really: all values of same order in each test-range must respond TRUE to their respective test to add 1 to the count, i.e. to get a yellow cell on the fourth row all three cells above must be yellow too (it's an implicit AND function), which is what all values of 2nd rank in their respective test-range do in the first part. In the second part you see that you don't have three yellow cells in any rank, therefore the count is 0.

Is this a question/report of a bug or just an explanation/thank you?

Are you getting all the numbers you expected if you do a manual calculation for comparison?

I see that your breakevens @ expiry (G4, L4, etc.) are identical for the first three then different in every leg afterwards, but my formula would probably yield the same result everywhere. If this need to be fixed you have to be much clearer about the source of data because your ROW thing (which should probably be COLUMN actually) is impossible to understand. Explain it in words not in formulas, like "breakeven @ expiry of leg 4 is the average of breakevens of Option/Assets #4 to #10 if they are not N/A" or something like this, and why #4 an not #3, you know what I mean?