Counting Multiple Records by specific criteria

[Nick]

Hi,

I'm having problems getting the right formula to suit what I need.

Basically I have a large spreadsheet containing many records and I want to
count the number of records that meet three specific types of criteria.

e.g.

Column A = Type
Column B = Status
Column C = Ref No. (i.e. 1.06)

The above columns have many different entries, therefore I would like to
count the number of records which meet the following criteria. No of Records
= Type A, Status X, Ref 1.06.

I have used SUMProduct, CountIF and etc but I don't seem to be doing it
correctly.

I have done this using PivotTables but I would like to add this formula to
another data table, hence the formulae question.

Many thanks in advance.

 

[Eduardo]

Hi,
try

=SUMPRODUCT((a1:a4="A")*(b1:b4="X")*(c1:c4=1.06))

change range to fit your needs, range has to be the same in every part of
the formula.
I checked and is working for me

 

[T. Valko]

Try something like this...

Use cells to hold the criteria to be counted:

E1 = some Type like A
F1 = some Status lkke X
G1 = some Ref No. like 1.06

=SUMPRODUCT(--(A1:A100=E1),--(B1:B100=F1),--(C1:C100=G1))

 

[Nick]

Thank you.....I was very close to getting it right.

 

[Nick]

Actually, this is a lot better for me to use as I seem to have a issue around
ref number in the formula. Using the below counts the correct figures.

Thank you very much for your assistance.

 

[T. Valko]

You're welcome. Thanks for the feedback!