Can I use COUNTIF for 2 values?

ICT-Craig

New Member
Joined
Nov 29, 2005
Messages
5
Hi,

I'm trying to gather info from a raw data sheet (sheet B), to create a summary sheet (sheet A).

Sheet B contains data in a 7 column/250 row array which is auto filtered.

So far I've managed to use COUNTIF in sheet A where the criteria is one column is sheet B.
But what I would like to do is use COUNTIF with criteria in 2 columns on sheet B?

Thanks in advance,

Craig (xl 2k)
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
For 2 criteria you need to use sumproduct - which will count the matching entries.
 

ICT-Craig

New Member
Joined
Nov 29, 2005
Messages
5
thanks for the reply :)

how would i impliment that against my current formula:
=COUNTIF('Sheet B'!C2:C250,B6)
[B6 is matching criteria on sheet A]

thanks
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
Could you post some sample data and what criteria you wish to filter on
 

ICT-Craig

New Member
Joined
Nov 29, 2005
Messages
5

ADVERTISEMENT





linkage corrected :oops: :)
 

ICT-Craig

New Member
Joined
Nov 29, 2005
Messages
5
i would like sheetA 'reserved' column to display the sum of username=reserved + unit=xxxxx

does that make sense? :confused: :)
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446

ADVERTISEMENT

Try

=SUMPRODUCT(--(Sheet1!B1:B9="reserved"),--(Sheet1!C1:C9="xxxxx"))
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
Basically, they change the trues and falses returned into 1 and 0's that can then be added.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,111
Messages
5,570,257
Members
412,313
Latest member
pauloalex
Top