Solved

Value of a column in Excel when using a sumif to compute

Posted on 2014-09-04
14
165 Views
Last Modified: 2014-09-04
Experts,

Is there a way to determine if the calculated amount of a cell is 0 when a formula using sumif is used to calculate the the value of a column?
0
Comment
Question by:morinia
[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
  • 8
  • 6
14 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40304103
After some thought, I removed my previous comment and will suggest this:

If the range you're testing has the possibility of zeros AND blanks, then yes, you can test for the distinction between the two like so:

=SUMIF(range,IF(testvalue="","",testvalue),sum_range)

This will return different results if your test value is a blank or zero.

Regards,
-Glenn
0
 

Author Comment

by:morinia
ID: 40304136
Glenn,

This is my formula


=IF( MONTH(1&"-"&Y$7) <  MONTH(TODAY()),SUMIFS(Claims,Month,MONTH(1&"-"&Y$7),EDIT,$C19)/SUMIFS(Claims,Month,MONTH(1&"-"&Y$7)),0)
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40304162
Oh, you're using a SUMIFS function, not a SUMIF function (as stated in the question). :-)

What does "EDIT" represent in your formula?

Does Y7 contain a year value?  If so, then MONTH(1&"-"&Y$7) will always evaluate to "1".
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40304204
Am I correct in assuming you're trying to avoid a divide by zero error in the second SUMIFS statement?  If so, you could use the IFERROR function to clean that up:
=IF( MONTH(1&"-"&Y$7) < MONTH(TODAY()),IFERROR(SUMIFS(Claims,Month,MONTH(1&"-"&Y$7),EDIT,$C19)/SUMIF(Month,MONTH(1&"-"&Y$7),Claims),0),0)

Note that I also changed the second function from SUMIFS to SUMIF, since you were only testing one criteria.

Regards,
-Glenn
0
 

Author Comment

by:morinia
ID: 40304253
Glenn,

I am sorry, I gave you the calculation for the percentage.

This is the formula for what I want to test.

This is a snapshot of the columns.  E7 as is the month


This is the formula which gives the result in E9  
=IF( MONTH(1&"-"&E$7) <  MONTH(TODAY()),SUMIFS(Claims,Month,MONTH(1&"-"&E$7),EDIT,$C9,ORIGIN,"OCR"),0)



I have attached a small sample of the spreadsheet and the data so you have an idea,  I could not post the actual spreadsheet.


The  columns on the data tab are assigned named fields respetively:

Edit    -   Edit
Claim_Source - Origin
Claim_Count  - Claims
Month - Month

$E7  is the month of January -  The values will only dislplay if the month is less than the current month, if it is not the value will be 0

The amount is claims is added if the origin = 'OCR' and Edit = the value in  $c9  and the month = 1 (JAN)
Formulas.xls
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40304342
I replicated the ranges you specified and the sample formula returned a result of 28, which seems correct for the specified criteria (i.e., Origin = "OCR", Edit = 001).

Now, this formula returns zero for 002, 005, & 006, but  I don't understand what the issue is, sorry.
0
 

Author Comment

by:morinia
ID: 40304396
My original questions was since each column has a formula in it, is there  a way to check if the value returned from the formula is 0?

I would like to delete all rows where there is no data for that edit (the results of the sumifs for Jan thru dec is 0

Is there a way to test the returned value, since there is a formula in the column not a value.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40304416
Well, yes, you can easily test to see if the result of the SUMIFS function is zero, but do you want to do if that is the case?  Do you want a different result to display (ex., blank cell)?

This, for example, would return a blank cell instead of a zero value:

=IF(MONTH(1&"-"&E$7) < MONTH(TODAY()),IF(SUMIFS(Claims,Month,MONTH(1&"-"&E$7),Edit,$C9,Origin,"OCR")=0,"",SUMIFS(Claims,Month,MONTH(1&"-"&E$7),Edit,$C9,Origin,"OCR")),0)
0
 

Author Comment

by:morinia
ID: 40304479
Glenn,

Thanks for the clarification, I would like to test for  blank, 0 or null.  Basically if the value is not > 0 .  In VB will the code see the formula or the value.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40304513
The original formula (and derivative copies):
=IF(MONTH(1&"-"&E$7) < MONTH(TODAY()),SUMIFS(Claims,Month,MONTH(1&"-"&E$7),Edit,$C9,Origin,"OCR"),0)
will only return a numeric value, never a blank.  However, there are two cases where the value can be zero:
1) If the month in row 7 is less than the current month (based on today's date)
2) if there are no values matching the Month, the Edit, and the Origin.

If you refer to the cell in VBA (ex. dblFreq = Range("E9").Value), you'll get the numeric value result of the formula.

Regards,
-Glenn
0
 

Author Comment

by:morinia
ID: 40304614
Thanks so much,

If currently I have code that was checking the row and column,  ie

If Cells(i, j) <> 0  

would I know code
if Range(Cells(i,j).value) <> 0      where I is the row and j is the column
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40304700
You'd just use
Cells(i,j).value <> 0

Open in new window


-Glenn
0
 

Author Closing Comment

by:morinia
ID: 40304791
Thanks so much.  It worked like a charm.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40304803
You're welcome.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cannot locate cell 15 41
script to read text file 34 29
How to turn this IF statement into a UDF? 5 19
Looping through files in a folder VBA 3 11
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

734 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