| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!D1:D30000) "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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!D1:D30000) "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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reverse Concatenation (Splitting Single Cell Data into Multiple Ce | Excel Discussion (Misc queries) | |||
| SUMPRODUCT, SUMIF, COUNTIF for multiple sheets for multiple criter | Excel Worksheet Functions | |||
| SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
| multiple SUMIF arguments | Excel Discussion (Misc queries) | |||
| Multiple SUMIF | Excel Worksheet Functions | |||
Linear Mode
