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

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?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel VBA DeveloperCommented:
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
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
Excel VBA DeveloperCommented:
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
Excel VBA DeveloperCommented:
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
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
Excel VBA DeveloperCommented:
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
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
Excel VBA DeveloperCommented:
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
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
Excel VBA DeveloperCommented:
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
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
Excel VBA DeveloperCommented:
You'd just use
``````Cells(i,j).value <> 0
``````

-Glenn
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Thanks so much.  It worked like a charm.
0
Excel VBA DeveloperCommented:
You're welcome.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.