Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
|
|||
|
|||
Thank you very much Claus. Worked perfect
|
#7
|
|||
|
|||
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing up subtotals according to conditions | Excel Programming | |||
Summing a range with conditions | Excel Worksheet Functions | |||
Summing a range with conditions | Excel Worksheet Functions | |||
Summing on multiple conditions | Excel Worksheet Functions | |||
Meeting two conditions before summing | Excel Worksheet Functions |