Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Changing Countif to Sumif formula so works if external workbook is closed

Posted on 2014-03-11
14
Medium Priority
?
2,195 Views
Last Modified: 2014-03-11
Workbook 2 (DailyMetrics.xlsm) contains the statistics based on data in Workbook 1 (CAP_29MAY.xlsm) using the "Countif" formula. But if Workbook 1 is closed, then Workbook 2 shows #VALUES instead of numbers. In researching this, it appears that Countif doesn't work when linking to closed Workbooks, but Sumif does. I am trying to convert my Countif formula to a Sumif formula, but the Sumif calculations vary from the Countif ones.

Countif formula: (result is "12")
=COUNTIFS(CAP_29MAY2014.xlsm!LOE_Range,"Major",CAP_29MAY2014.xlsm!MasterCompUpdateRange,"<>",CAP_29MAY2014.xlsm!ADC_AMD_Range,"ADC*")

Sumif formula: (result is "0")
=SUM(IF(CAP_29MAY2014.xlsm!LOE_Range="Major",IF(CAP_29MAY2014.xlsm!MasterCompUpdateRange="<>",IF(CAP_29MAY2014.xlsm!ADC_AMD_Range="ADC*",0)*1,0)))

I need to know if I'm correct in saying that the Sumif formula will work if Workbook 1 is closed. If not, then is there an alternative formula that can be implemented to work under these conditions? And if so, then how do I translate my Countif formula to a Sumif formula correctly so I get the same results?

Thanks,
Andrea
0
Comment
Question by:Andreamary
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
14 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 39921428
Can you post a sample workbook to know a bit what your talking about ?
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39921435
offhand
you have COUNTIFS tha tis the formula you first referred to

But after you show
=SUM(IF(CAP_29MAY2014.xlsm!LOE_Range="Major",IF(CAP_29MAY2014.xlsm!MasterCompUpdateRange="<>",IF(CAP_29MAY2014.xlsm!ADC_AMD_Range="ADC*",0)*1,0)))

This formula is not SUMIFS !!! it is a SUM that have IF statement, that is nothing to do with SUMIFS.

So I suggest you post both workbook the one that have the formula and the one that have the data so I can make some tests for you and get you what you need.

gowflow
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39921440
I believe the SUMIF formula in the syntax that you have needs to be array entered by pressing Ctrl Shift & Enter to confirm it. When doing so Excel will put {  } brackets around the formula.

However, you don't have a SUM Range at the end of it, you have 3 criteria:
LOE_Range = "Major"
MasterCompUpdateRange ="<>" (Iassume you are aiming for not blank)
ADC_AMD_Range = "ADC"
Sum Range ???

The equivalent to your COUNTIFS would be SUMIFS but unfortunately, you are right that the SUMIFS doesn't work when source workbook is closed.

The other option would be to use SUMPRODUCT with similar syntax:

=SUMPRODUCT((LOE_Range="Major")*(MasterCompUpdateRange="ADC")*(ADC_AMD_Range="ADC")*SumRange)

Thanks
Rob H
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:Andreamary
ID: 39921480
Thanks for the quick feedback and explanation that my SUMIF formula isn't going to perform as a COUNTIF.

Rob, your interpretation of the criteria is correct.

Since my goal is to find a formula that works with a closed Workbook, which COUNTIF and SUMIF won't do, I tried the SUMPRODUCT formula above and it returned a #NAME? error. Any advice as to why that might be happening?

Thanks,
Andrea
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39921496
try your countIF formula as array
click on the cell that have the countif formula press F2 then Press CTRL SHIFT ENTER at the same time
then drag down. sometime COUNTIF as array work on closed workbooks.
gowflow
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 39921562
Hello Andrea,

None of the COUNTIF/SUMIF/AVERAGEIF "family" of formulas works with closed workbooks - that's because those functions expect ranges (not arrays) as the arguments and when the ranges are retrieved from closed workbooks they become arrays, which doesn't work.

I think you can use SUMPRODUCT, as Rob says, but one of your criteria uses a wildcard - that won't work in SUMPRODUCT (or within a SUM9IF array formula) so you need LEFT function - try like this:

=SUMPRODUCT((CAP_29MAY2014.xlsm!LOE_Range="Major")*(CAP_29MAY2014.xlsm!MasterCompUpdateRange<>"")*(LEFT(CAP_29MAY2014.xlsm!ADC_AMD_Range,3)="ADC"))

regards, barry
0
 

Author Comment

by:Andreamary
ID: 39921568
Hi gowflow,

I tried the COUNTIF as an array, but unfortunately it didn't make a difference. The #VALUE errror message cropped up when Workbook 1 was closed.

Andrea
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39921603
Hello Andrea,

Did you see my reply above?

regards, barry
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39921939
Hi Barry,

I could have guessed you would be able to contribute when SUMPRODUCT was mentioned. Is that one of your search terms by any chance? I wasn't aware of the Wild Card restriction in SUMPRODUCT.

Thanks
Rob
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39921943
Andrea, apologies if you misunderstood my suggestion and just copied and pasted what I wrote; that was only intended as a guide for the syntax.

I had missed the Wild Card element of the ADC range anyway!!

Thanks
Rob
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39922018
Hello Rob,

Actually it's not strictly SUMPRODUCT where the problem lies - you can't use a wildcard when doing a straight comparison with =

So, for example, if you use this formula

=A1=xyz*

that will only be true if A1 contains literally xyz*

regards, barry
0
 

Author Closing Comment

by:Andreamary
ID: 39922096
Hi Barry,

Thanks so much. This solution works exactly as hoped! BTW, I must have been posting my response to a previous suggestion at the same time you posted your solution, since it's only now, logging back in after my commute, that I'm seeing your answer...

Thanks to everyone else who offered suggestions...

Andrea
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39922143
Barry,

How is your SUMPRODUCT getting a number when the three columns its referring to are text?

Unless of course the column that is being checked for "not blank" is a number. If so, could this criterion go? If it were blank, it would return zero anyway, wouldn't it?

Thanks
Rob
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39922164
Hello Rob,

In this case the SUMPRODUCT formula is simply replicating a COUNTIFS formula, i.e. counting rows where multiple conditions apply.

SUMPRODUCT can be used for counting or summing so the equivalent of

=COUNTIFS(A1:A10,"x",B1:B10,"y")

is just

=SUMPRODUCT((A1:A10="x")*(B1:B10="y"))

in which the two arrays are multiplied against each other, thereby returning 1 for each row where both conditions are met, and when summed giving a count of rows that meet both conditions.

......but if you want to sum column C in rows where those conditions apply you can use SUMIFS

=SUMIFS(C1:C10,A1:A10,"x",B1:B10,"y")

or the SUMPRODUCT equivalent

=SUMPRODUCT((A1:A10="x")*(B1:B10="y"),C1:C10)

regards, barry
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question