Counting Multiple Records by specific criteria
 

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.

Counting Multiple Records by specific criteria
 

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

"Nick" wrote:

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.

Counting Multiple Records by specific criteria
 

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

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
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.

Counting Multiple Records by specific criteria
 

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

"Eduardo" wrote:

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

"Nick" wrote:

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.

Counting Multiple Records by specific criteria
 

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" wrote:

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

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
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.




.

Counting Multiple Records by specific criteria
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
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.




.