Sumproduct formula to select data dynamically

[narsing rao]

i have below formula but here i have to mention the row range ,is there any way it finds it dynamically.

=SUMPRODUCT(--(sheet2!G2:G87=1)*(INT(sheet2!L2:L87)=TODAY()-1))

i have one more formula here i want the formula should calculate the row count after the above formula row count dynamically

=COUNTIFS(Sheet2!C:C,"sev-1",Sheet2!B:B,">=1-Mar-2017",Sheet2!B:B,"<31-Mar-2017")

i mean if its selects the row count G2:G87 NEXT formula should calculate the row count from G89 to till end of the data, it should happen dynamically because row keep chaining dialy.

 

[Chihiro]

Use dynamic named range.

Formula will depend on what's contained in the range (and if there's any blanks mixed in range etc).

Basic formula for dynamic named range.
=sheet2!$G$2:INDEX(sheet2!$G:$G,COUNTA(sheet2!$G:$G))

This is when there's no blanks in range and there is header row (G1) that has string.

I'd usually name it with prefix "lst" (ex. lstDates)

Then you can use the name in your formula.

Alternately convert data range to Excel table format and use structured reference.

 

[narsing rao]

Hi,

for both the formulas header will be same,it looks like below .

first formula counts the number of calls from H3:H5 and second formula should count the calls H9: till the calls count , this data keeps changing like calls count will increase or decrease daily. that's why i want it dynamically count.