creating a function with multiple criteria
 

I am new to functions and formula. I want to write a simple formula that
will calculate the number of times a particular value occurs in a spreadsheet
using multiple criteria. I want to write a function that would calculatethe
number of times this situation is met-- how often does cvg, i, p and ch occur
in the same row.

cef i p me
cef i f me
cgs d f me
cgs d p ch
cvg d p ch
cvg i p ch
cvg i p ch

i hope this isn't too confusing... can anyone help?

Hi!

Try this:

=SUMPRODUCT(--(A1:A7="cvg"),--(B1:B7="I"),--(C1:C7="p"),--(D1:D7="ch"))

Better to use cells to hold the criteria:

H1 = cvg
I1 = i
J1 = p
K1 = ch

Then:

=SUMPRODUCT(--(A1:A7=H1),--(B1:B7=I1),--(C1:C7=J1),--(D1:D7=K1))

Biff

"e_bone75" wrote in message
...
I am new to functions and formula. I want to write a simple formula that
will calculate the number of times a particular value occurs in a
spreadsheet
using multiple criteria. I want to write a function that would
calculatethe
number of times this situation is met-- how often does cvg, i, p and ch
occur
in the same row.

cef i p me
cef i f me
cgs d f me
cgs d p ch
cvg d p ch
cvg i p ch
cvg i p ch

i hope this isn't too confusing... can anyone help?

There are certainly multiple ways to do this, but here is one that I
think is pretty easy to understand for an Excel beginner:

Put this in E1:
=AND(A1="cvg",B1="i",C1="p",D1="ch") This will put the word true in
the cell if all criteria are met, and the word false if they are not
all met.
Copy the formula to all the rows.

And then count the number of times the formulas returned "True" in
another cell; something like this:

=COUNTIF(E1:E200,"true")


--
Lilliabeth
------------------------------------------------------------------------
Lilliabeth's Profile: http://www.excelforum.com/member.php...o&userid=27741
View this thread: http://www.excelforum.com/showthread...hreadid=476354