DCOUNT alternative for a closed source value error (Excel)

[gemexcel]

Hello,

I have a DCOUNT formula which looks in another sheet. It shows a VALUE error when the second sheet is closed. Much a like a sumif or countif.

What is the best alternative for this formula?

=DCOUNT('LINK'!$A:$Q,MATCH($C$30,'LINK'!$1:$1,0),'LINK'!$D$1:$D$2)

Thank you!

 

[shobolos]

Can you post a sample of your data and results expected for both spreadsheets.

 

[gemexcel]

Thank you for your response, I hope this helps...

I have my data on one workbook and then have another workbook with an overview in. This has has a cell with a drop down menu so you can select from a list of products.

I want to be able to show the total number of accounts which list the product that is selected in this drop down menu. I had used a dcount with the criteria ">0.1". I can not longer do this as the data comes from a different workbook and a DCOUNT formula shows as VALUE.

If 'Product 2' was selected in the drop down then I want to return '4'. If it was 'Product 1' then is should be '3'.

Here is an example of my data layout;

Sales Month to Date	Product 1	Product 2	Product 3	Product 4
Account 1	2	4
Account 2	4	5		8
Account 3	3	4	2	5
Account 4		1	7	5

<TBODY>
</TBODY>

Thanks! <TBODY> </TBODY>

<TBODY>
</TBODY>

 

[shobolos]

Excel 2010
	A	B	C	D	E
1	Sales Month to Date	Product 1	Product 2	Product 3	Product 4
2	Account 1	2	4
3	Account 2	4	5		8
4	Account 3	3	4	2	5
5	Account 4		1	7	5
6
7	Product 1	3
8	Product 2	4
9	Product 3	2
10	Product 4	3
Sheet1

Cell Formulas
Range	Formula
B7	=COUNTIF(OFFSET($A$2,,MATCH(A7,$B$1:$E$1),4),">"&0)

 

[gemexcel]

Thank you for your response but this doesn't solve my problem as COUNTIF does not work when the source workbook is closed.

I also don't want all the the Products listed like that, the formula needs to pick up the correct product from a cell e.g. B7. B7 is a drop down list so could change and therefore the $A$2 part of your formula would not work.

Unless I am misstaken, I am no expert!

Thanks again.

 

[FormR]

This appears to work with closed workbooks using XL2013.

=SUMPRODUCT(--(INDEX([Book1.xlsx]Link!B2:Q100,0,MATCH(C30,[Book1.xlsx]Link!B1:Q1,0))>0))

Note: Do not use full column or full row references with this type of array processing formula.

 

[gemexcel]

Thank you so much, that works perfectly!!!