Solved Just need totals by year very large database w/300 sheets

Microsoft Office 2010 professional (full...
January 29, 2015 at 16:23:22
Specs: Windows 7
i have a table basically column A date, column B cash, column c Check. I need to look at column B & C in same row and sum all based on whether column A's year text only, like 2015.so it would give me a total of all payouts in 2015 only. the date column is formatted 1-jul-2015 with multiple years being listed.

date cash check
1-jul-2015 50.00 0
1/jan-2014 0 100
1-feb-2105 0 75

total for 2015 should be 125.00


See More: Just need totals by year very large database w/300 sheets

Report •


#1
January 29, 2015 at 19:22:34
Try this, it's a bit long, but should work for you as long as you have Excel 2007 or newer:

=SUMIFS($B$1:$B$3,$A$1:$A$3,">="&DATE(2015,1,1),$A$1:$A$3,"<="&DATE(2015,12,31))+SUMIFS($C$1:$C$3,$A$1:$A$3,">="&DATE(2015,1,1),$A$1:$A$3,"<="&DATE(2015,12,31))

MIKE

http://www.skeptic.com/


Report •

#2
January 30, 2015 at 12:09:48
✔ Best Answer
Thanks but i figured it out
=SUMPRODUCT((YEAR($B$13:$B$200)=2014)*($C$13:$C$200+$D$13:$D$200))

Report •
Related Solutions


Ask Question