Search code examples
excelif-statementexcel-formulasumexcel-indirect

Getting numbers of rows in SUMIF range from different cells

I use a SUMIF function of the kind:

=SUMIF('Sheet1'!D54:D63, "QueryText", 'Sheet1'!J54:J63)

My problem is that the ranges D54:D63 and J54:J63 will change so I want to get the number of rows from some other cell. e.g. In cell A1 I would have 54, in cell A2 I would have 63 and my formula should be:

=SUMIF('Day by day results'!D(VALUEOF(A1)):D((VALUEOF(A2)), "Day Game", 'Day by day results'!J(VALUEOF(A1)):J(VALUEOF(A2)))

Obviously the VALUEOF(A1) does not work there, that's just for the example of what I want to achieve.

Solution

  • Please try: 

    =SUMIF(INDIRECT("Sheet1!D"&A1&":D"&A2),"QueryText",INDIRECT("Sheet1!J"&A1&":J"&A2))