# Can I use COUNTIF for 2 values?

#### ICT-Craig

##### New Member
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?

Craig (xl 2k)

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
For 2 criteria you need to use sumproduct - which will count the matching entries.

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

thanks

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

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

does that make sense?

Try

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

nice one, thanks for your help GorD

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

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

Replies
5
Views
216
Replies
3
Views
126
Replies
1
Views
152
Replies
1
Views
373
Replies
12
Views
744

1,203,483
Messages
6,055,675
Members
444,807
Latest member
RustyExcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back