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))

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))

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))


.

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))


.

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

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