CODEWITHSUNDEEP
excelexcel-formula
viper941
viper941

Reputation: 156

Creating a countifs formula for a closed workbook

Situation - I have an Excel workbook that is a daily log of various individuals work. It contains a simple list of 3 columns: Item Number, Name, Date.

I have a second workbook that I am trying to use create a look up tool to gather individual counts on an as needed basis. Because Excel does not support using the Countifs formula on closed workbooks I attempted using an array instead, but I am not getting the expected results.

Using =COUNTIFS([Tech_Log_Master_Sheet.xlsx]Handbag!$C:$C,$D$4,[Tech_Log_Master_Sheet.xlsx]Handbag!$D:$D,$D$5) I get the result I expect.

Using {=COUNT(IF(AND([Tech_Log_Master_Sheet.xlsx]HandBag!$C$2:$C$20001=D4,[Tech_Log_Master_Sheet.xlsx]HandBag!$D$2:$D$20001=D5),1,0))} I get 1 as a result no mater what I have in D4 or D5.

I got the idea to do this by researching how to use a Countif on a closed workbook and using the Array was what was suggested. Adding the AND was my idea to expand on the concept. I am not sure if it is an issue with the array or because that answer appears to be based off of Excel 2007 and I am using 2013.

Upvotes: 1

Views: 4846

Answers (1)

Jonathan
Jonathan

Reputation: 1025

Your issue is with the 'AND'. Consider a shorter table:

Name    Date
John    1st Jan
John    1st Jan
John    2nd Jan

Now:

=and(Name='John',Date='1st Jan')
=({TRUE, TRUE, TRUE},{TRUE, TRUE, FALSE})
=FALSE

because these are not identical.

Try:

=SUMPRODUCT((Name='John')*(Date='1st Jan'))

or for your case:

=SUMPRODUCT(([Tech_Log_Master_Sheet.xlsx]HandBag!$C$2:$C$20001=D4)*([Tech_Log_Master_Sheet.xlsx]HandBag!$D$2:$D$20001=D5))

For more details on sumproduct, which is effectively an array formula that doesn't need to be entered as an array formula, see here: http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

Upvotes: 1

Related Questions