Multiple conditions with COUNTIF where a second sheet is use

Keith_H

New Member
Joined
Jan 20, 2005
Messages
8
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Jan 20, 2005
Messages
8
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!!! (y)

Keith
 

Keith_H

New Member
Joined
Jan 20, 2005
Messages
8
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!!! (y)

Keith
 

Forum statistics

Threads
1,148,525
Messages
5,747,206
Members
424,068
Latest member
Salim khamis

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top