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

 Salary Surveys Property & Casualty, Life, Health & Pension Health Actuary JobsInsurance & Consulting jobs for Students, Associates & Fellows Actuarial Recruitment Visit DW Simpson's website for more info. www.dwsimpson.com/about Casualty JobsProperty & Casualty jobs for Students, Associates & Fellows

#1
06-29-2018, 05:00 PM
 rhoucag Member Join Date: Oct 2001 Posts: 276
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, 05:05 PM
 DyalDragon Member SOA Join Date: Apr 2009 Location: Here Studying for the hell of it... College: AASU Favorite beer: This one... Posts: 32,850

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 Predictive Analytics
VEE FAP FAP FAP FAP FAP FAP FAP FAP APC
#3
06-29-2018, 05:17 PM
 Dr T Non-Fan Member SOA AAA Join Date: Sep 2001 Location: Just outside of Nowhere Posts: 97,206

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

"45 es un titere" -- Seal of The President of The United States of America protest art
#4
06-29-2018, 05:19 PM
 DyalDragon Member SOA Join Date: Apr 2009 Location: Here Studying for the hell of it... College: AASU Favorite beer: This one... Posts: 32,850

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 Predictive Analytics
VEE FAP FAP FAP FAP FAP FAP FAP FAP APC
#5
06-29-2018, 05:29 PM
 Childish Gambino Member SOA Join Date: Jul 2014 Posts: 31,177

if(sumproduct(--(isblank(A:A)))>0,"error","it's all good fam")
__________________
Quote:
 Originally Posted by pete5383 if you supported Trump, you should be profoundly embarrassed by your poor decision making.
#6
06-29-2018, 05:30 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,320

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, 06:40 PM
 rhoucag Member Join Date: Oct 2001 Posts: 276

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 06:45 PM..
#8
06-29-2018, 06:49 PM
 Dr T Non-Fan Member SOA AAA Join Date: Sep 2001 Location: Just outside of Nowhere Posts: 97,206

Quote:
 Originally Posted by DeepPurple Sumproduct becomes is more useful than sumif. Period.
Fixed.
__________________
"Facebook is a toilet." -- LWTwJO

"45 es un titere" -- Seal of The President of The United States of America protest art
#9
06-29-2018, 06:52 PM
 rhoucag Member Join Date: Oct 2001 Posts: 276

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, 08:41 PM
 JMO Carol Marler Non-Actuary Join Date: Sep 2001 Location: Back home again in Indiana Studying for Nothing actuarial. Posts: 37,643

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.