Can I use COUNTIF for 2 values?

[ICT-Craig]

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)

 

[GorD]

For 2 criteria you need to use sumproduct - which will count the matching entries.

 

[ICT-Craig]

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]

Could you post some sample data and what criteria you wish to filter on

 

[ICT-Craig]

linkage corrected

 

[ICT-Craig]

i would like sheetA 'reserved' column to display the sum of username=reserved + unit=xxxxx

does that make sense?

 

[GorD]

Try

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

 

[ICT-Craig]

nice one, thanks for your help GorD

what are the "--" for, out of interest?

 

[GorD]

Basically, they change the trues and falses returned into 1 and 0's that can then be added.