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

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

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

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

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

Thank you very much Claus. Worked perfect