creating a function with multiple criteria

[e_bone75]

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?

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
>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?
>

[Lilliabeth]

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")