Counting Issue

[cmk18]

I have a spread sheet with 3 columns (date, incident type, and region.)
I want to count the number of times that a particular incident happen
in each region. I don't want to use a pivot table because the shee
will consistently grow, and I would like to have this metri
automatically update, rather than having to refresh it. I used counti
and offset for the incidents per region per month (the sheet is srote
by date.) I was curious if there is a way to use possibly Countif o
any other function to count across these two columns.

Thank

 

[Andy Brown]

I don't want to use a pivot table because the sheet

will consistently grow, and I would like to have this metric
automatically update, rather than having to refresh it.

On one hand it depends if by "refresh" you really mean refresh
(recalculate), or actually mean redefine the source data range.

You might like to try a dynamic named range for the source data. If the
labels are in A1:C1, then Insert -- Name -- Define. Name = "Data" (w/out
quotes), Refers to =

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),3)

, and click OK. Then you can use "Data" as the range in Step2 of the Pivot
Wizard, it'll automatically adjust as you add or remove data. The only
refreshing you'll need to do is when you subsequently rightclick the table.

HTH,
Andy