countif/sumproduct on multiple criteria
 

looking for help with following

a1 b1 c1
x r m
x s m
t r m
x r d
y r m

The problem is I want to count the occurences of m
if (a1 = x and b1 = r) and if (a1 = y and b1 =r)
the solution I am looking for is ( 2 in the above table)
I have about 500 lines of data to process
* note ( range of a1 cell are merged ,ie: a1+a2 a3+a4 ect..)

any solution would be helpful.

Hi Phil

bit confused on the merging comment
do you have
.........A............B...............C
1....x..............r..................m
2....................s..................m
3....t...............r...................m
4...................r......................d

where A1 & A2, A3 & A4 are merged but columns B & C aren't ... in this case
i'm confused as to how A3 has t and A4 has x as per your example ..... and
i'm not sure an answer is easily possible

if the cells aren't merged then the formula would simply be along the lines
of
=SUMPRODUCT(--(A8:A12="x"),--(B8:B12="r"),--(C8:C12="m"))
this formula would work in A1&A2, B1 & B2, C1&C2 are merged but not if
A1&A2 are merged and B1, B2, C1, C2 aren't

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Phil.M" wrote in message
...
looking for help with following

a1 b1 c1
x r m
x s m
t r m
x r d
y r m

The problem is I want to count the occurences of m
if (a1 = x and b1 = r) and if (a1 = y and b1 =r)
the solution I am looking for is ( 2 in the above table)
I have about 500 lines of data to process
* note ( range of a1 cell are merged ,ie: a1+a2 a3+a4 ect..)

any solution would be helpful.

=SUMPRODUCT(--((A2:A10="x")+(A2:A10="y")0),--(B2:B10="r"),--(C2:C10="m"))

or


=SUMPRODUCT((A2:A10={"x","y"})*(B2:B10="r")*(C2:C1 0="m"))

I don't know what you mean by merged, if indeed you mean merged as in
formatcellsalignment and merge? If so unmerge the cells, remember nothing
good comes from merging cells

--
Regards,

Peo Sjoblom


"Phil.M" wrote in message
...
looking for help with following

a1 b1 c1
x r m
x s m
t r m
x r d
y r m

The problem is I want to count the occurences of m
if (a1 = x and b1 = r) and if (a1 = y and b1 =r)
the solution I am looking for is ( 2 in the above table)
I have about 500 lines of data to process
* note ( range of a1 cell are merged ,ie: a1+a2 a3+a4 ect..)

any solution would be helpful.