Dynamic range of sumif

[koskesh]

Hi, I have an excel workbook with sumifs that look through the whole column to find the sum.
The data source changes every month and is huge (and slow). I want to speed it up a little bit with a dynamic range instead of searching the whole column

So instead of F:F something like COUNTA(..)
is this possible? I couldnt find a way to do it:

=SUMIFS('APO vs Budget'!$F:$F;'APO vs Budget'!$R:$R;"Yes";'APO vs Budget'!$P:$P;$B8)

Thanks

 

[Asheesh]

Table option...click anywhere on the source data then Go to "Insert" Tab then Go to "Tables" Group and click on "Table"

Once done..use the table ranges...instead of complete column reference..

FOr details..you may want to go through the below link

http://chandoo.org/wp/2009/09/10/data-tables/

http://www.techrepublic.com/blog/10-things/10-reasons-to-use-excels-table-object/

 

[koskesh]

Thanks, great idea.. so simple but so good

 

[Asheesh]

You welcome

 

[NARAYANK991]

Hi ,

Using tables gives you many benefits , but a faster SUMIF(S) is not one of them.

The SUMIF family of functions is very efficient even when dealing with full column references , unlike SUMPRODUCT. Check this link :

https://msdn.microsoft.com/en-us/library/office/ff726673(v=office.14).aspx

Narayan