Count

[Pammy]

I have a large spreadsheet dating back from 2006, is there a way I can search
by a Detectives Name which is in Col K and then count the assigned offenses
in Col G, example:
Detective John Brown col k and then search col G, AA (Agg Assault) and give
me a total for Agg Assault, then change col G for Burg (Burglary) and give me
a total for Burglary, etc.
I have been counting by doing a filter, but I felt there could be an easier
way.
Thanks,

 

[Mike H]

Pammy,

Try this

=SUMPRODUCT(--($K$2:$K$40="Detective John Brown"),--($G$2:$G$40="AA"))

However it would be nore flexible if the search strings were cell addresses
so also try this
=SUMPRODUCT(--($K$2:$K$40=A1),--($G$2:$G$40=A2))

Where A1 = The Detective
A2 = the offence code

Mike

 

[John C]

You could set up a new tab that would do nothing but tally. Assumptions, tab
name for sheet currently with all the data is DATA tab. New tab name is TALLY.

Starting in cell A2 on tab TALLY, and going down, key in all the detectives
names (Note, you need to ensure the detective names are identical to what was
used on tab DATA). Starting in Cell B1, and going across, type in the
different types of offense that will be obtained from col G on tab DATA.
(i.e: in B1, type AA, in C1, type Burg, etc.).

Then, in cell B2 (intersection between AA and first detective name), type
the following formula:
=SUMPRODUCT(--(Data!$A$2:$A$9999=$A2),--(Data!$G$2:$G$9999=B$1))
Copy to the right and down as needed.

Note: If you don't have xl2007, then your cell references must be defined
similar to above(obviously, if you have more than 9999 rows of data on data
tab, extend as needed). I believe (as I have read it, but I don't have
xl2007), that if you do have xl2007 that you could just use the Data!$A:$A,
and are not limited to having to put a finite row selection.

Hope this helps!