Update sumproduct formula with new ranges automatically

[kidwispa]

Hi All,

I have to update a worksheet every day with new data received in columns A-C going down. On the same sheet at the top, there is a table that pulls through information from these columns using SUMPRODUCT. Now what I would like to do is automatically update the formulas to include the new data as I update it.

The formula I am currently using is:

=SUMPRODUCT(--($B$2:$B$572=G3),--($C$2:$C$572<>""))

Can this be done in the formula or would I need to use VBA?

Thanks for any help.

 

[Andrew Poulsom]

You could try using a dynamic named range:

http://www.contextures.com/xlNames01.html#Dynamic

 

[baitmaster]

sumproduct can be extended to the whole column in XL10, but its a slow formula so I wouldn't use it here. Try =COUNTIFS(B:B,G3,C:C,"<>"&"") instead

 

[MrVillareal]

You can also use the table or list features in excel for your data input..