If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple Sumif with concatenation
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(tblPr ocessorActivity!$C:$C))&RIGHT('Processor Time Allocation'!B$6,1))),tblProcessorActivity!$D$1:$D$ 2999)) |
#2
|
|||
|
|||
Multiple Sumif with concatenation
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 ... 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(tblPr ocessorActivity!$C:$C))&RIGHT('Processor Time Allocation'!B$6,1))),tblProcessorActivity!$D$1:$D$ 2999)) |
#3
|
|||
|
|||
Multiple Sumif with concatenation
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 ... 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(tblPr ocessorActivity!$C:$C))&RIGHT('Processor Time Allocation'!B$6,1))),tblProcessorActivity!$D$1:$D$ 2999)) . |
#4
|
|||
|
|||
Multiple Sumif with concatenation
=SUM(IF((tblProcessorActivity!$B$1:$B$2999="CG")* (tblProcessorActivity!$N$1:$N$2999=(MONTH(MAX(tblP rocessorActivity!$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 ... 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 ... 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(tblPr ocessorActivity!$C:$C))&RIGHT('Processor Time Allocation'!B$6,1))),tblProcessorActivity!$D$1:$D$ 2999)) . |
#5
|
|||
|
|||
Multiple Sumif with concatenation
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!D130000) "T. Valko" wrote: =SUM(IF((tblProcessorActivity!$B$1:$B$2999="CG")* (tblProcessorActivity!$N$1:$N$2999=(MONTH(MAX(tblP rocessorActivity!$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 ... 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 ... 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(tblPr ocessorActivity!$C:$C))&RIGHT('Processor Time Allocation'!B$6,1))),tblProcessorActivity!$D$1:$D$ 2999)) . -- Dave Peterson |
#6
|
|||
|
|||
Multiple Sumif with concatenation
OK
Thanks, Dave! -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... 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!D130000) "T. Valko" wrote: =SUM(IF((tblProcessorActivity!$B$1:$B$2999="CG")* (tblProcessorActivity!$N$1:$N$2999=(MONTH(MAX(tblP rocessorActivity!$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 ... 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 ... 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(tblPr ocessorActivity!$C:$C))&RIGHT('Processor Time Allocation'!B$6,1))),tblProcessorActivity!$D$1:$D$ 2999)) . -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|