Sumif question

sharshra

Active Member
Joined
Mar 20, 2013
Messages
391
Office Version
  1. 365
I'm using Sumif function to calculate the amount for each year based on the data for months. For doing this, I'm calculating the year based on the months & then using the year for Sumif function. It is giving me the correct result.

However, I would like to avoid calculating the year separately. Instead, would it be better to get the year from the months & use it in the Sumif function?

Sample table is shown below.

At present, I'm using Year() function to calculate the year of the date in a separate column.
Excel Formula:
=YEAR(B2)

This value of year is used in Sumif function. Instead, wanted to derive year from the date & use it in Sumif function. With this, I need not have a separate column for year.
Excel Formula:
=SUMIF($D$2:$D$10,G2,$C$2:$C$10)
 

Attachments

  • year table.jpg
    year table.jpg
    4.3 KB · Views: 8
  • base table.jpg
    base table.jpg
    15.6 KB · Views: 8

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
How about
Excel Formula:
=SUMIFS(C:C,B:B,">="&DATE(G2,1,1),B:B,"<"&DATE(G2+1,1,1))
 
Upvote 0
Thanks, Fluff. I'm getting 0 when same formula is applied. Not sure what is going wrong :unsure:
 
Upvote 0
In that case can you post some sample data using the XL2BB add-in.
 
Upvote 0
I have installed XL2BB but 'capture range' is disabled :( How can I enable it?
 

Attachments

  • xl2bb disabled.png
    xl2bb disabled.png
    18.8 KB · Views: 5
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top