Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Excel Sumif Question

Posted on 2013-11-07
Medium Priority
322 Views
My criteria column has dates. I would like to sum the adjacent column based on the year.
Thank you.
0
Question by:rjfields
[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

LVL 23

Accepted Solution

Ejgil Hedegaard earned 1000 total points
ID: 39631478
Use an array formula
=SUM(IF(YEAR(A1:A10)=2013,B1:B10,0))
Dates in range A1:A10 and the values to sum in B1:B10
Enter the formula with Ctrl+Shift+Enter
0

LVL 23

Assisted Solution

NBVC earned 1000 total points
ID: 39631485
Try Sumproduct

e.g.

=SUMPRODUCT(--(YEAR(A1:A10)=2013),B1:B10)
0

LVL 23

Expert Comment

ID: 39631491
Or if you want SUMIFS

e.g. =SUMIFS(B1:B10,A1:A10,">=1/1/2013",A1:A10,"<=12/30/2013")
0

LVL 12

Expert Comment

ID: 39631648
Hi NB_VC,

What a creative way of using SUMPRODUCT!

0

LVL 23

Expert Comment

ID: 39631673
It's called a "double unary".  It is basically a double negative.  It coerces the results of the condition (which normally gives TRUE/FALSE values) into 1/0 results respectively.  This way the SUMPRODUCT can do it's math.

There are other ways to:

for example

SUMPRODUCT(1*(YEAR()...

or SUMPROCUCT(0+(YEAR().....
0

Author Closing Comment

ID: 39631798
Thank you very much.
0

LVL 12

Expert Comment

ID: 39632105
Thanks for that explanation, NB_VC.

Well said, but I still don't fully understand the need for it, because if I put 1/1/2013 in A1, these work as is:
=(YEAR(A1)=2013)*3      => Result is 3
=(YEAR(A1)=2012)*3      => Result is 0
Any ideas why the above work as is, but SUMPRODUCT() with ranges needs the '--'?

I'm using Excel 2003.  All the latest, a decade ago.

PS: I don't really need this, but wouldn't mind understanding.

Thanks.
tel2
0

LVL 23

Expert Comment

ID: 39633252
The * in *3 is actually coercing the (YEAR(A1)=2013) from TRUE to 1, then it multiplies by 3.  When you use operators like * , + , - , /, then the need for the -- is eliminated, because those operators are actually doing the coercing.  Remember the -- is a double negative, so it with the first - it coerces and converts the 1's to -1's, so you need another - to revert them back to +1's.
0

LVL 12

Expert Comment

ID: 39634637
OK - thanks NB_VC!
0

## Featured Post

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
###### Suggested Courses
Course of the Month9 days, 16 hours left to enroll