creating a function with multiple criteria

[Guest]

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"),--(D17="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),--(D17=K1))

Biff

 

[Lilliabeth]

There are certainly multiple ways to do this, but here is one that
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 i
the cell if all criteria are met, and the word false if they are no
all met.
Copy the formula to all the rows.

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

=COUNTIF(E1:E200,"true"