Sumproduct cell range grows and calculation takes too long

I have this Sumproduct formula below

=SUMPRODUCT(--(TEXT(AccompReport!$B$2:$B$10000,"mmm")=C$1),--(AccompReport!$F$2:$F$10000="Juan Dela Cruz")*(AccompReport!$E$2:$E$10000))

=SUMPRODUCT(--(AccompReport!$C$2:$C$10000="Assistance"),--(TEXT(AccompReport!$B$2:$B$10000,"mmm")=B$1))


that set's to this "$B$2:$B$10000" range but my records can reach beyond this range, it's more than 10,000 rows... the problem is, from this formula it took long time to calculate those... any one can help me on how to make the cell range auto adjusted?... or any way to make the caculation fast... I'm using a VBA Userform for encoding... i include "Application.Calculation = xlCalculationManual" and "Application.Calculation = xlCalculationAutomatic"... it makes a little help but still needs to wait from calculation... any help?...

Hi rhonalddean,

You can use defined names which will automatically include the increasing / decreasing ranges.. press ctrl + F3 to open name manager and use the logic as ... =offset($B$2,0,0,counta($b:$b)-1,1)... adjust it accordingly as per you actual data spread as I have written as a guess

In case of any issues, upload a sample workbook.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

I found one like this from "name manager" =OFFSET(AccompReport!$F$2,0,0,COUNTA(AccompReport!$F:$F),1) and it's working... what about your code "=offset($B$2,0,0,counta($b:$b)-1,1)" with "-1" what's the difference?... Thanks for the reply...


Edit: Great! i understand what "-1" does... much better... problem solved!

BTW the auto calculation works much faster now...

As I said that was just a formula based on my guess.. I introduced -1 there just to remove the header row

Regards,
DILIPandey

<click on below 'star' if this helps>

I agree.. this issue is there with all the defined names and that's why you need to use the count / counta on the column which will not have any blank entries

Regards,
DILIPandey

<click on below 'star' if this helps>

I agree.. this issue is there with all the defined names and that's why you need to use the count / counta on the column which will not have any blank entries

Regards,
DILIPandey

<click on below 'star' if this helps>

Here is a solution for this blanks issue :-

See attached file and use the dynamic defined name "data".. thanks.
dynamic name to handle blanks.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

Ok.. where to look into the attachment.. i.e, which sheet / row /column / cell ? Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

how to count the filled cells that can ignore the cells with formula?...

Excel 2013 has ISFORMULA function which can check if a cell has formula or not ..
Alternatively, you can use Len function to check if a cell has formula returning blank, it will always return 0 OR there are other ways which can be used. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Yes. there is a way to ignore the formulated cells. I had already replied about that in my post #8. There is an attachment as well. Please look into that.

Suggestion:- As per me, there is not much considerable difference between SumIF and SumProduct.. though SumIF is faster but calculation speed also depends on your system configuration

Idea:- Turn off the auto calculation and press Shift + F9 which will calculate current worksheet only

Regards,
DILIPandey

<click on below 'star' if this helps>

Shift + F9 is itself a "push of button(s)"

Regards,
DILIPandey

<click on below 'star' if this helps>