# Multiple conditions with COUNTIF where a second sheet is use

#### Keith_H

##### New Member
Greetings to all forum members.

Topic/issue:

Multiple conditions with COUNTIF where a second sheet is used as the data source

I am trying to create a spreadsheet with two sheets, one is the dataset and the second will contain summaries. These summaries are based on multiple condition COUNTIF or SUMIF formulae.

A colleague has an old 1-2-3 spreadsheet which functions in this manner. To take the example of the formula which would appear in cell C16 of the second sheet, a formula like the following would appear in 1-2-3:

@DCOUNT(dataset:A1..dataset:E10,"Transactioncode",MONTH=C1#AND#APPROVED="Yes")

I have been able to get an equivalent formula to work in Excel:

=SUMPRODUCT(--(A1:A10=C14),--(B1:B10="Yes"))

The problem that I have is that I am unable to modify this formula to reference an external sheet; I get #VALUE!

Please can you advise how, having the dataset in sheet 1 (which I have named dataset), I can add a multiple condition fomula into sheet 2 (which I have named summary) which refers back to sheet 1.

Best regards

Keith
dashboard using DSUM.xls
ABCDE
1MonthApproved
2JanYes
3JanNo
4JanNo
5JanYes
6JanYes
7FebNo
8FebYes
9FebYes
10FebYes
11
12
13
14JanFeb
15
16Qtyofrequests54
17
18Qtyapprovedinmonth3
19
20
Sheet1

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

##### MrExcel MVP
Re: Multiple conditions with COUNTIF where a second sheet is

Prefix the ranges with the relevant file+sheet name something like...

=SUMPRODUCT(--([Book.xls]Sheet1!A1:A10=C14),--([Book.xls]Sheet1!B1:B10="Yes"))

#### Keith_H

##### New Member
Re: Multiple conditions with COUNTIF where a second sheet is

Superb!

I used

=SUMPRODUCT(--(dataset!A1:A10=C14),--(dataset!B1:B10="Yes"))

and got the result I was after.

Many thanks!!!

Keith

#### Keith_H

##### New Member
Re: Multiple conditions with COUNTIF where a second sheet is

Superb!

I used

=SUMPRODUCT(--(dataset!A1:A10=C14),--(dataset!B1:B10="Yes"))

and got the result I was after.

Many thanks!!!

Keith

Replies
7
Views
1K
Replies
4
Views
216
Replies
4
Views
526
Replies
8
Views
245
Replies
2
Views
472

1,181,785
Messages
5,932,036
Members
436,815
Latest member

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