Prev: Average range of discontinuous cells
Next: Copy If rows to another worksheet
From: MrRJ on 11 May 2010 15:41
Hello,

I produced a formula that I need to have expanded.
=SUM('2010 Actuals'!E2:INDEX('2010 Actuals'!E2:Q2,MATCH(Sheet1!C5,'2010
Actuals'!E1:Q1,0)))

I would like to do another condition based on my Category (E). It needs to
sum all values in the given Category. As you can see, I have one condition
for Period, which is C5.

I somehow see the use of Sumproduct formula, but cannot visualize it.

Can you please assist. Thanks
From: Ashish Mathur on 11 May 2010 20:41
Hi,

Could you show us how the data is arranged and what the data is exactly

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"MrRJ" <MrRJ(a)discussions.microsoft.com> wrote in message
news:DEE95636-07DA-46F2-855D-4D7DE7C416AC(a)microsoft.com...
> Hello,
>
> I produced a formula that I need to have expanded.
> =SUM('2010 Actuals'!E2:INDEX('2010 Actuals'!E2:Q2,MATCH(Sheet1!C5,'2010
> Actuals'!E1:Q1,0)))
>
> I would like to do another condition based on my Category (E). It needs
> to
> sum all values in the given Category. As you can see, I have one
> condition
> for Period, which is C5.
>
> I somehow see the use of Sumproduct formula, but cannot visualize it.
>
> Can you please assist. Thanks

From: MrRJ on 12 May 2010 09:21
Ashish,
Here is what the layout would be. I use drop down lists for DC and Period.
So, if I select DC_A and P5. I should see the results YTD P1-P5.

Master Sheet
DC Category ValueA ValueB
DC_A Cat5 need to see YTD based on Period selection.
DC_A Cat10 need to see YTD based on Period selection.
DC_A Cat15 need to see YTD based on Period selection.

ValueA is derived from another sheetA which looks like this.
DC Category P1 P2 P3 P4......P13
DC_A Cat5 $ $ $ $ $
DC_A Cat10 $ $ $ $ $
DC_A Cat15 $ $ $ $ $

ValueB is derived from another sheetB which looks just like sheetA with
different data.

Hope this explains things.
I appreciate your help.


"Ashish Mathur" wrote:

> Hi,
>
> Could you show us how the data is arranged and what the data is exactly
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
>
> "MrRJ" <MrRJ(a)discussions.microsoft.com> wrote in message
> news:DEE95636-07DA-46F2-855D-4D7DE7C416AC(a)microsoft.com...
> > Hello,
> >
> > I produced a formula that I need to have expanded.
> > =SUM('2010 Actuals'!E2:INDEX('2010 Actuals'!E2:Q2,MATCH(Sheet1!C5,'2010
> > Actuals'!E1:Q1,0)))
> >
> > I would like to do another condition based on my Category (E). It needs
> > to
> > sum all values in the given Category. As you can see, I have one
> > condition
> > for Period, which is C5.
> >
> > I somehow see the use of Sumproduct formula, but cannot visualize it.
> >
> > Can you please assist. Thanks
>
 | 
Pages: 1
Prev: Average range of discontinuous cells
Next: Copy If rows to another worksheet