Multiple criteria Data analysis

  • Thread starter Thread starter DuncanH
  • Start date Start date
D

DuncanH

I have a multi-comparision problem to solve comprising a
number of records (rows) of events over an extended period
of time.
Column A has a list of several Names in text format some
having multiple instances
Column B has a Date in text format eg 23jan04 (to allow me
to find all dates in eg Jan)
Column C has one or more EventCodes "a" thru "z" in one
cell also in text format, (with spaces between if multiple
EventCodes occur )

I need to analyse data as follows:

For each Name, count the number of occurences in jan
analysed for each EventCode.

I am trying to use a formula along the lines of
=count(IF(A:A,="Name1"), IF(B:B,="*jan*"), IF
(C:C, "*h*),0,0,0)

I use wild cards to find the "jan" in the text string and
the "h" EventCode in the potentially many EventCodes in
the one cell C.

This will then allow me to build a table.
I have been unable to find the magic formula to solve this
convoluted problem (having tried at least 30 alternative
approachs as a result of searches at MS and on the web).

Any help will be greatly appreciated
 
use this formula


=SUMPRODUCT((A1:A19="NAME")*(B1:B19="MONTH")*(C1:C19="CODE"))

Replace name with the actual name
Replace Month with the Month
Replace code with the Code

This will give you a coulnt of all rows which meet the criteria

Randall
 
Assuming your date cell (text) always has a 3 character month beginning in
the 3rd position:

=SUMPRODUCT(($A$3:$A$500="Name1")*(MID($B$3:$B$500,3,3)="Jan")*($C$3:$C$500=
"h"))

Copy this down eleven rows, substituting the other months of the year.

This should help..
 
Almost there thank you.
However, your proposed solution does not handle the
situation of having the C data conents of the form {a f g}
in the one cell - a normal condition
I hope for a solution that can determine the total number
of ocurences of one EventCode when occuring alone or with
others.
 
A step in the right direction thank you.
The proposed solution does not handle the situation of
having the date by the day (as per JMay's) and also does
not handle the C data contents of the form {a f g} in the
one cell - a normal condition
I hope for a solution that can determine the total number
of ocurences of one EventCode when occuring alone or with
others.
 
Back
Top