sumproduct ignoring new data

Hi there
new data is entered at row 7 at top of data table(Table-Transactions tab) - the sumproduct formula is ignoring the new row 7 - on Kills Report line 1n should show 5 Rats - have checked the format, date ranges, pest types etc & unable to identify any problem - formula work correctly before new data added.

Any help appreciated
cheers

Thanks Glenn - that was quick - haven't tried it as yet'
Shouldn't need 'Cat' in 'shaded cell' table is simple data & sumproduct should easily handle the multiple criteria - are you able to suggest any reasons for it not working? do I need to send more info/data?
I really would like to get to the bottom of this.
cheers
Chris

Chris, not sure if you're trying to resolve a #VALUE! error or something else -- I get a #VALUE! error and this is because some of your "summation" range holds null strings, rather than blanks, which can't be coerced (via *)

So, modifying your formula per below (red) and applying to matrix - returns results:

Formula:

Please Login or Register  to view this content.

I am assuming you're opting for SUMPRODUCT on grounds that the source file may, at times, be closed?

If, conversely, you're saying you physically insert a new row 7 when adding data then the above formula will ignore the new row (and shift to row 8 etc)

Hi Xlent - many thanks for your suggestions - yes source file often closed. A new row is inserted in the table by macro from a data capture screen after many checks are made for accuracy of input. The table data is shifted down with a line insert & the formatting only is copied to the new inserted line - not all columns of this new line may be filled - it may be these 'empty' fields (that are holding null values as u suggest) causing the problem. The formulae in the 'Trap Report' referencing the range are absolute so don't increment (ie: $P$7 ) - also I am not getting any error messages at all. Will try a little routine to 'clear contents' of those 'blank cells' after writing new data to line 7 which should leave formatting in place - see if that resolves the issue - thanks again - cheers Chris