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

Search Actuarial Jobs by State @ DWSimpson.com:
AL AK AR AZ CA CO CT DE FL GA HI ID IL IN IA KS KY LA
ME MD MA MI MN MS MO MT NE NH NJ NM NY NV NC ND
OH OK OR PA RI SC SD TN TX UT VT VA WA WV WI WY

#1
06-29-2018, 06:00 PM
 rhoucag Member Join Date: Oct 2001 Posts: 274
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,527

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...
__________________
PRELIMS VEE FAP FAP FAP FAP FAP FAP FAP FAP

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: 93,163

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,527

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.
__________________
PRELIMS VEE FAP FAP FAP FAP FAP FAP FAP FAP

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: 25,647

if(sumproduct(--(isblank(A:A)))>0,"error","it's all good fam")
__________________
When fascism comes to America, it will be wrapped in the US Flag and carrying a Cross.
#6
06-29-2018, 06:30 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,210

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: 274

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: 93,163

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: 274

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,605

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 Apr 5, 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 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.