CODEWITHSUNDEEP
excel
Bastien Vandamme
Bastien Vandamme

Reputation: 18485

SUMIFS a specific YEAR

Could you help me to fix this excel error?

I'm trying to write a SUMIFS function. On my first page I have all my banking transaction sorted by date from 2011 to 2014. All transactions belong to one and only one category.

I need to sum my value in Sum_range only if my two criteria are true.

  1. The year of the date should be the year specified in a cell
  2. The category should be the category specified in another cell

I know the problem is about the year or the date because I can sum with my second condition only.

enter image description here

Upvotes: 1

Views: 4563

Answers (2)

user4039065
user4039065

Reputation:

While SUMPRODUCT can use YEAR like this,

=SUMPRODUCT((YEAR('Transactions LU'!A:A)=A12)*('Transactions LU'!J:J=C11), 'Transactions LU'!H:H)

... you won't want to use full column references (slower) and SUMPRODUCT can easily break trying to perform math on an extra text value. SUMIFS is vastly more efficient and in the area of 30% of the calculation load.

=SUMIFS('Transactions LU'!H:H, 'Transactions LU'!A:A, ">="&DATE(A12, 1, 1), 'Transactions LU'!A:A, "<"&DATE(A12 +1, 1, 1), 'Transactions LU'!J:J, C11)

The second SUMIFS formula is much preferred.

Upvotes: 2

lobochrome
lobochrome

Reputation: 55

Don't 100% understand the issue - but if it is really only the Year column that is making trouble try using Value(A12) or if that doesn't work TEXT(A12;0) this either converts the value to text or into number format. It depends whether your data column is in number or text format. (probably try TEXT first).

Upvotes: 0

Related Questions