From: ram on 23 Apr 2010 10:54 Hi All, Is it possible to use concatenation with multiple sumif? I have the following formula but it doesn't work with the concatenation, any suggestion of what I'm doing wrong Thanks in advance for any help =SUM(IF((tblProcessorActivity!\$B\$1:\$B\$2999="CG")*(tblProcessorActivity!\$N\$1:\$N\$2999=(MONTH(MAX(tblProcessorActivity!\$C:\$C))&RIGHT('Processor Time Allocation'!B\$6,1))),tblProcessorActivity!\$D\$1:\$D\$2999)) From: T. Valko on 23 Apr 2010 16:21 There's nothing wrong with the formula syntax so you'll have to explain in more detail what "doesn't work" means. -- Biff Microsoft Excel MVP "ram" wrote in message news:8AEC733D-8EB0-4C41-96D3-7EFEC0B45F44(a)microsoft.com...> Hi All, > > Is it possible to use concatenation with multiple sumif? > > I have the following formula but it doesn't work with the concatenation, > any > suggestion of what I'm doing wrong > > Thanks in advance for any help > > > =SUM(IF((tblProcessorActivity!\$B\$1:\$B\$2999="CG")*(tblProcessorActivity!\$N\$1:\$N\$2999=(MONTH(MAX(tblProcessorActivity!\$C:\$C))&RIGHT('Processor > Time Allocation'!B\$6,1))),tblProcessorActivity!\$D\$1:\$D\$2999)) From: ram on 23 Apr 2010 17:53 Hi It doesn't sum any numbers because in the critera It needs to compare the week number and month. If i use only the month comparison I get a result of 2 which is correct. When I use the concatenation and right function it returns zero. When I just use the right function for the criteria it also only returns zero but it should return 2 in all these examples. Thanks for your help "T. Valko" wrote: > There's nothing wrong with the formula syntax so you'll have to explain in > more detail what "doesn't work" means. > > -- > Biff > Microsoft Excel MVP > > > "ram" wrote in message > news:8AEC733D-8EB0-4C41-96D3-7EFEC0B45F44(a)microsoft.com... > > Hi All, > > > > Is it possible to use concatenation with multiple sumif? > > > > I have the following formula but it doesn't work with the concatenation, > > any > > suggestion of what I'm doing wrong > > > > Thanks in advance for any help > > > > > > =SUM(IF((tblProcessorActivity!\$B\$1:\$B\$2999="CG")*(tblProcessorActivity!\$N\$1:\$N\$2999=(MONTH(MAX(tblProcessorActivity!\$C:\$C))&RIGHT('Processor > > Time Allocation'!B\$6,1))),tblProcessorActivity!\$D\$1:\$D\$2999)) > > > . > From: T. Valko on 23 Apr 2010 20:10 >=SUM(IF((tblProcessorActivity!\$B\$1:\$B\$2999="CG")*(tblProcessorActivity!\$N\$1:\$N\$2999=(MONTH(MAX(tblProcessorActivity!\$C:\$C))&RIGHT('Processor Time Allocation'!B\$6,1))),tblProcessorActivity!\$D\$1:\$D\$2999)) Ok, I'm assuming that tblProcessorActivity!\$C:\$C contains dates. You're getting the month number of the max date: =(MONTH(MAX(tblProcessorActivity!\$C:\$C)) Then yo're concatenating that with: &RIGHT('Processor Time Allocation'!B\$6,1) So, what's in 'Processor Time Allocation'!B\$6 ? -- Biff Microsoft Excel MVP "ram" wrote in message news:7F810D3C-F542-484A-B7DC-C50EE1519C78(a)microsoft.com...> Hi > It doesn't sum any numbers because in the critera It needs to compare the > week number and month. If i use only the month comparison I get a result > of 2 > which is correct. When I use the concatenation and right function it > returns > zero. When I just use the right function for the criteria it also only > returns zero but it should return 2 in all these examples. > > Thanks for your help > > > > "T. Valko" wrote: > >> There's nothing wrong with the formula syntax so you'll have to explain >> in >> more detail what "doesn't work" means. >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "ram" wrote in message >> news:8AEC733D-8EB0-4C41-96D3-7EFEC0B45F44(a)microsoft.com... >> > Hi All, >> > >> > Is it possible to use concatenation with multiple sumif? >> > >> > I have the following formula but it doesn't work with the >> > concatenation, >> > any >> > suggestion of what I'm doing wrong >> > >> > Thanks in advance for any help >> > >> > >> > =SUM(IF((tblProcessorActivity!\$B\$1:\$B\$2999="CG")*(tblProcessorActivity!\$N\$1:\$N\$2999=(MONTH(MAX(tblProcessorActivity!\$C:\$C))&RIGHT('Processor >> > Time Allocation'!B\$6,1))),tblProcessorActivity!\$D\$1:\$D\$2999)) >> >> >> . >> From: Dave Peterson on 24 Apr 2010 07:31 Biff, In a different thread the OP said that it was text in the header that was causing the =month() portion to fail. Although, the formula changed, too. =SUMPRODUCT((tblProcessorActivity!B1:B30000="CG") *(MONTH(tblProcessorActivity!C1:C30000)=MONTH(MAX(tblProcessorActivity!C:C))) *(tblProcessorActivity!N1:N30000=4), tblProcessorActivity!D1:D30000) "T. Valko" wrote:> > >=SUM(IF((tblProcessorActivity!\$B\$1:\$B\$2999="CG")*(tblProcessorActivity!\$N\$1:\$N\$2999=(MONTH(MAX(tblProcessorActivity!\$C:\$C))&RIGHT('Processor > Time Allocation'!B\$6,1))),tblProcessorActivity!\$D\$1:\$D\$2999)) > > Ok, I'm assuming that tblProcessorActivity!\$C:\$C contains dates. > > You're getting the month number of the max date: > > =(MONTH(MAX(tblProcessorActivity!\$C:\$C)) > > Then yo're concatenating that with: > > &RIGHT('Processor Time Allocation'!B\$6,1) > > So, what's in 'Processor Time Allocation'!B\$6 ? > > -- > Biff > Microsoft Excel MVP > > "ram" wrote in message > news:7F810D3C-F542-484A-B7DC-C50EE1519C78(a)microsoft.com... > > Hi > > It doesn't sum any numbers because in the critera It needs to compare the > > week number and month. If i use only the month comparison I get a result > > of 2 > > which is correct. When I use the concatenation and right function it > > returns > > zero. When I just use the right function for the criteria it also only > > returns zero but it should return 2 in all these examples. > > > > Thanks for your help > > > > > > > > "T. Valko" wrote: > > > >> There's nothing wrong with the formula syntax so you'll have to explain > >> in > >> more detail what "doesn't work" means. > >> > >> -- > >> Biff > >> Microsoft Excel MVP > >> > >> > >> "ram" wrote in message > >> news:8AEC733D-8EB0-4C41-96D3-7EFEC0B45F44(a)microsoft.com... > >> > Hi All, > >> > > >> > Is it possible to use concatenation with multiple sumif? > >> > > >> > I have the following formula but it doesn't work with the > >> > concatenation, > >> > any > >> > suggestion of what I'm doing wrong > >> > > >> > Thanks in advance for any help > >> > > >> > > >> > =SUM(IF((tblProcessorActivity!\$B\$1:\$B\$2999="CG")*(tblProcessorActivity!\$N\$1:\$N\$2999=(MONTH(MAX(tblProcessorActivity!\$C:\$C))&RIGHT('Processor > >> > Time Allocation'!B\$6,1))),tblProcessorActivity!\$D\$1:\$D\$2999)) > >> > >> > >> . > >> -- Dave Peterson