Multiple conditions with COUNTIF where a second sheet is use

[Keith_H]

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
	A	B	C	D	E
1	Month	Approved
2	Jan	Yes
3	Jan	No
4	Jan	No
5	Jan	Yes
6	Jan	Yes
7	Feb	No
8	Feb	Yes
9	Feb	Yes
10	Feb	Yes
11
12
13
14			Jan	Feb
15
16	Qtyofrequests		5	4
17
18	Qtyapprovedinmonth		3
19
20
Sheet1

 

[Aladin Akyurek]

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]

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]

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