Actuarial Outpost Excel SUMIF - get to return error if missing
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

 Upload your resume securely at https://www.dwsimpson.com to be contacted when new jobs meet your skills and objectives.

#1
06-29-2018, 06:00 PM
 rhoucag Member Join Date: Oct 2001 Posts: 275
Excel SUMIF - get to return error if missing

I want to use SUMIF in Excel because I'd like to combine certain combinations. For example - Company A reserve plus Company B reserve. But I want the formula to return an error if either of the two are missing as both are required. Any thoughts other than using another formula ?
#2
06-29-2018, 06:05 PM
 DyalDragon Member SOA Join Date: Apr 2009 Location: Here Studying for the hell of it... College: AASU Favorite beer: This one... Posts: 31,818

Quote:
 Originally Posted by rhoucag I want to use SUMIF in Excel because I'd like to combine certain combinations. For example - Company A reserve plus Company B reserve. But I want the formula to return an error if either of the two are missing as both are required. Any thoughts other than using another formula ?
Example?

I can think of a few ways, but are you just summing up a particular month? Is it columns of data or just two values? Do you want it to update manually or automatically whenever the time periods change? etc, etc...
__________________
P FM MFE MLC C VEE FAP FAP FAP FAP FAP FAP FAP FAP
Predictive Analytics

Quote:
 Originally Posted by Androzani Major Maybe a better statement is that I enjoyed having experienced it both ways?
#3
06-29-2018, 06:17 PM
 Dr T Non-Fan Member SOA AAA Join Date: Sep 2001 Location: Just outside of Nowhere Posts: 94,360

What would be the reason that one of the two would be missing? Is that an actual error that requires further looking up? If so, should do that separately.
__________________
"Facebook is a toilet." -- LWTwJO
#4
06-29-2018, 06:19 PM
 DyalDragon Member SOA Join Date: Apr 2009 Location: Here Studying for the hell of it... College: AASU Favorite beer: This one... Posts: 31,818

Quote:
 Originally Posted by Dr T Non-Fan What would be the reason that one of the two would be missing? Is that an actual error that requires further looking up? If so, should do that separately.
I assumed info obtained from two different sources, but am curious as well.
__________________
P FM MFE MLC C VEE FAP FAP FAP FAP FAP FAP FAP FAP
Predictive Analytics

Quote:
 Originally Posted by Androzani Major Maybe a better statement is that I enjoyed having experienced it both ways?
#5
06-29-2018, 06:29 PM
 Childish Gambino Member SOA Join Date: Jul 2014 Posts: 27,158

if(sumproduct(--(isblank(A:A)))>0,"error","it's all good fam")
__________________
Quote:
 Originally Posted by Pseudolus Trump's biggest weakness is that he utterly lacks the knowledge, experience, interest, attention span, temperament, and common decency to be the marginally effective President of a reasonably-large HOA.
#6
06-29-2018, 06:30 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,266

Sumproduct becomes more useful than sumif when you want to start customizing the calculation.

Your original post wasn't clear on exactly what you meant. If you are summing up two ranges, just sum them. What is the condition you want to meet with the If part?

This works if you want to force an error when a field is 0 or missing

=sumproduct(range, range, 1/range).

If that doesn't give you what you need, post some ficticious data that demonstrates the input and your desired output.
__________________
Come on. Let's go space truckin'. Come on!
#7
06-29-2018, 07:40 PM
 rhoucag Member Join Date: Oct 2001 Posts: 275

Thanks. I need to first pull various items for a couple of legal entities on one sheet. If one items gets forgotten or deleted, the sumif won't warn you. And most internal checks will still work since they will essentially treat the reserve as "zero" and internal consistency is maintained. There are also 3 scenarios I need on this item sheet, and I want to pull each scenario's sum separately.

SUMIF/SUMPRODUCT is desired since some items are the sums of multiple components (sometimes 2, 3, 4, or more).

Last edited by rhoucag; 06-29-2018 at 07:45 PM..
#8
06-29-2018, 07:49 PM
 Dr T Non-Fan Member SOA AAA Join Date: Sep 2001 Location: Just outside of Nowhere Posts: 94,360

Quote:
 Originally Posted by DeepPurple Sumproduct becomes is more useful than sumif. Period.
Fixed.
__________________
"Facebook is a toilet." -- LWTwJO
#9
06-29-2018, 07:52 PM
 rhoucag Member Join Date: Oct 2001 Posts: 275

Quote:
 Originally Posted by Dr T Non-Fan Fixed.
Agreed, except that SUMPRODUCT can get very nonintuitive for someone who isn't that comfortable with Excel. Once this gets handed over to another person/team or goes through an audit, it may be a blocker. Some of the other users here have a habit of changing the whole setup when they encounter a formula they don't understand.
#10
06-29-2018, 09:41 PM
 JMO Carol Marler Non-Actuary Join Date: Sep 2001 Location: Back home again in Indiana Studying for Nothing actuarial. Posts: 37,660

Could one use VBA (or something) to check that all required data is present?
__________________
Carol Marler, "Just My Opinion"

Pluto is no longer a planet and I am no longer an actuary. Please take my opinions as non-actuarial.

My latest favorite quotes, updated Nov. 20, 2018.

Spoiler:
I should keep these four permanently.
Quote:
 Originally Posted by rekrap JMO is right
Quote:
 Originally Posted by campbell I agree with JMO.
Quote:
 Originally Posted by Westley And def agree w/ JMO.
Quote:
 Originally Posted by MG This. And everything else JMO wrote.
And this all purpose permanent quote:
Quote:
 Originally Posted by Dr T Non-Fan Yup, it is always someone else's fault.
MORE:
All purpose response for careers forum:
Quote:
 Originally Posted by DoctorNo Depends upon the employer and the situation.
Quote:
 Originally Posted by El Actuario Therapists should ask the right questions, not give the right answers.
Quote:
 Originally Posted by Sredni Vashtar I feel like ERM is 90% buzzwords, and that the underlying agenda is to make sure at least one of your Corporate Officers is not dumb.