Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Senior Member
 
Posts: 105
Default Summing with conditions

Hello Friends,

Please refer file uploaded. I would need a formula to total summing up of months columns using special conditions based on the description given in the "Result's tab. These formulas are to appear in columns AL and AM

Also if you could suggest the SUM IF formula I currently have from B to X are perfect. It serves my purpose, just wondering if there is anything better, like a index match .

http://s000.tinyupload.com/?file_id=...19969658067114

Thanks
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Summing with conditions

hi,

Am Thu, 17 Sep 2015 07:55:46 +0100 schrieb Excel Dumbo:

http://s000.tinyupload.com/?file_id=...19969658067114


in AL3:
=IF(ISNUMBER(SEARCH("FULL",AL$1)),SUMPRODUCT(--(YEAR($B$1:$AJ$1)=2015),$B3:$AJ3),IF(RIGHT(AL$1,1) ="1",SUMPRODUCT(--(YEAR($B$1:$AJ$1)=2015),--(MONTH($B$1:$AJ$1)<=6),$B3:$AJ3),SUMPRODUCT(--(YEAR($B$1:$AJ$1)=2015),--(MONTH($B$1:$AJ$1)6),$B3:$AJ3)))
and copy to AM3


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #3   Report Post  
Senior Member
 
Posts: 105
Default

Thank you very much Claus. Your super formula did the trick in column AL.
But in column AM, the formula gives the same answer as column AL

Just to clarify. Column AL is for CY (calendar year 15) which means Jan 15 to Dec 15. The formula works perfect here

Column AM is for FY (financial year 15-16) which means Jul 14 to Jun 15.
Different period range here. So if I copy formula from column AL to AM, givers me the same answer as AL

New file attached which shows this http://s000.tinyupload.com/?file_id=...74053143686186

Thanks heaps. Always been of great assistance
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Summing with conditions

Hi,

Am Thu, 17 Sep 2015 23:44:04 +0100 schrieb Excel Dumbo:

Thank you very much Claus. Your super formula did the trick in column
AL.
But in column AM, the formula gives the same answer as column AL


sorry, I did not read carefully.
Try in AM3:
=IF(RIGHT($AM$1,1)="1",SUMPRODUCT(--(YEAR(B1:AK1)=2014),--(MONTH(B1:AK1)6),B3:AK3),IF(RIGHT($AM$1,1)="2",SU MPRODUCT(--(YEAR(B1:AK1)=2015),--(MONTH(B1:AK1)<7),B3:AK3),SUMPRODUCT(--(YEAR(B1:AK1)=2014),--(MONTH(B1:AK1)6),B3:AK3)+SUMPRODUCT(--(YEAR(B1:AK1)=2015),--(MONTH(B1:AK1)<7),B3:AK3)))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Summing with conditions

Hi again,

Am Fri, 18 Sep 2015 09:13:00 +0200 schrieb Claus Busch:

sorry, I did not read carefully.


I forgot to set the first row absolut:

in AL3:
=IF(ISNUMBER(SEARCH("FULL",$AL$1)),SUMPRODUCT(--(YEAR(B$1:AK$1)=2015),B3:AK3),IF(RIGHT($AL$1,1) ="1",SUMPRODUCT(--(YEAR(B$1:AK$1)=2015),--(MONTH(B$1:AK$1)<=6),B3:AK3),SUMPRODUCT(--(YEAR(B$1:AK$1)=2015),--(MONTH(B$1:AK$1)6),B3:AK3)))

In AM3:
=IF(RIGHT($AM$1,1)="1",SUMPRODUCT(--(YEAR(B$1:AK$1)=2014),--(MONTH(B$1:AK$1)6),B3:AK3),IF(RIGHT($AM$1,1)="2", SUMPRODUCT(--(YEAR(B$1:AK$1)=2015),--(MONTH(B$1:AK$1)<7),B3:AK3),SUMPRODUCT(--(YEAR(B$1:AK$1)=2014),--(MONTH(B$1:AK$1)6),B3:AK3)+SUMPRODUCT(--(YEAR(B$1:AK$1)=2015),--(MONTH(B$1:AK$1)<7),B3:AK3)))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


  #6   Report Post  
Senior Member
 
Posts: 105
Default

Thank you very much Claus. Worked perfect
  #7   Report Post  
Junior Member
 
Posts: 1
Default

http://abecarcarecenter.net/
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Summing up subtotals according to conditions Daisy Excel Programming 0 July 8th 08 11:15 AM
Summing a range with conditions rb Excel Worksheet Functions 2 March 27th 06 12:07 AM
Summing a range with conditions bpeltzer Excel Worksheet Functions 1 March 26th 06 11:44 PM
Summing on multiple conditions Stacy Excel Worksheet Functions 1 September 15th 05 08:24 PM
Meeting two conditions before summing Andeb Excel Worksheet Functions 1 June 24th 05 10:33 AM


All times are GMT +1. The time now is 12:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"