CODEWITHSUNDEEP
excelexcel-formula
capm
capm

Reputation: 1047

Apply a function to an argument inside a formula

How can I apply a function to an argument of a SUMIF or COUNTIF formula? For example:

=SUMIF(YEAR(B1:B10),"2017", A1:A10)

Where B1:B10 contains an array of dates. For example:

1   A    B          
2   200  01/01/2017
3   300  01/01/2017
4   420  01/01/2016
5   250  01/01/2016

When I try:

=SUMPRODUCT(A:A*(YEAR(B:B)=2017))

or

=SUMPRODUCT(A1:A5*(YEAR(B1:B5)=2017))

I get #REF! however if I define the ranges like:

=SUMPRODUCT(A2:A5*(YEAR(B2:B5)=2017))

I get the result I expect.

Upvotes: 1

Views: 79

Answers (3)

A.S.H
A.S.H

Reputation: 29352

Use SUMPRODUCT with arrays. Multiplying by a boolean array converts it to 0 or 1 array:

=SUMPRODUCT(A1:A10*(YEAR(B1:B10)=2017))

p.s.: this is a valid normal formula, no need for CSE.

On the other hand, SUMIF and SUMIFS want their range arguments to be pure ranges, not arrays.

So it appears that your data is heterogeneous and some cells are not numbers or not dates. To deal with this, try this array formula:

=SUM(IF(ISNUMBER(A1:A5), A1:A5, 0)*(YEAR(IF(ISNUMBER(B1:B5),B1:B5,0))=2017))
Ctrl+Shift+Enter

Upvotes: 3

hackwithharsha
hackwithharsha

Reputation: 920

No need to use CTRL + SHIFT + ENTER ( CSE )

=SUMPRODUCT(--(TEXT(B7:B10,"YYYY")="2017")*A1:A10)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152605

If you really like the SUMIF then you will need to use SUMIFS and bracket the date:

=SUMIFS(A:A,B:B,">=1/1/2017",B:B,"<=12/31/2017")

You can do the same with COUNTIFS.

Upvotes: 1

Related Questions