I need help with the following: I am working in a workbook that was created
by someone else. The summary tab is built using Insert Define Name to extract
information from the source tab (OppDB). On a separate tab, I need to
extract the number of products and Factored MRR for specific groups. I've
tried to use what was already built: =DSUM(OppDB,"Factored MRR", P1:P2) and
it's not working P1:P2 houses the specific group.

I've also tried the following:
=SUMPRODUCT(Opportunity!E3:E135="ICDS")*(Opportuni ty!C3:C135="SCG")
=SUM(IF($B$1=Segment, IF(Products=A3, Count)))

With the above formulas I'm attempting to count the number of products
(ICDS) for a specific group (SCG), independently of what has already been
done. Opportunity is the tab that houses the source data.

Any assistance will be greatly appreciated!
--
Thanks In Advance, Annie

Try
=SUMPRODUCT(--(Opportunity!E3:E135="ICDS"),--(Opportunity!C3:C135="SCG"))


"Annie" wrote:

I need help with the following: I am working in a workbook that was created
by someone else. The summary tab is built using Insert Define Name to extract
information from the source tab (OppDB). On a separate tab, I need to
extract the number of products and Factored MRR for specific groups. I've
tried to use what was already built: =DSUM(OppDB,"Factored MRR", P1:P2) and
it's not working P1:P2 houses the specific group.

I've also tried the following:
=SUMPRODUCT(Opportunity!E3:E135="ICDS")*(Opportuni ty!C3:C135="SCG")
=SUM(IF($B$1=Segment, IF(Products=A3, Count)))

With the above formulas I'm attempting to count the number of products
(ICDS) for a specific group (SCG), independently of what has already been
done. Opportunity is the tab that houses the source data.

Any assistance will be greatly appreciated!
--
Thanks In Advance, Annie

it works... AWESOME!!! How about the Factored MRR? What formula can I use
to sum the Factored MRR if the department is SCG.
Thanks, Annie


"Sheeloo" wrote:

Try
=SUMPRODUCT(--(Opportunity!E3:E135="ICDS"),--(Opportunity!C3:C135="SCG"))


"Annie" wrote:

I need help with the following: I am working in a workbook that was created
by someone else. The summary tab is built using Insert Define Name to extract
information from the source tab (OppDB). On a separate tab, I need to
extract the number of products and Factored MRR for specific groups. I've
tried to use what was already built: =DSUM(OppDB,"Factored MRR", P1:P2) and
it's not working P1:P2 houses the specific group.

I've also tried the following:
=SUMPRODUCT(Opportunity!E3:E135="ICDS")*(Opportuni ty!C3:C135="SCG")
=SUM(IF($B$1=Segment, IF(Products=A3, Count)))

With the above formulas I'm attempting to count the number of products
(ICDS) for a specific group (SCG), independently of what has already been
done. Opportunity is the tab that houses the source data.

Any assistance will be greatly appreciated!
--
Thanks In Advance, Annie

Use
=DSUM(OppDB,"Factored MRR", P1:P2)
after entering the Header label for department in cell P1 and SCG in P2...
This will give you TOTAL MRR in rows for department=SCG

See DSUM help to understand how it works...

You can also use SUMPRODUCT like this
=SUMPRODUCT((Opportunity!A3:A135"),--(Opportunity!C3:C135="SCG"))
assuming MMR is in Col A

-- converts the values to 1 or 0... so use it when counting... remove it
from one set if you want to SUM




"Annie" wrote:

it works... AWESOME!!! How about the Factored MRR? What formula can I use
to sum the Factored MRR if the department is SCG.
Thanks, Annie


"Sheeloo" wrote:

Try
=SUMPRODUCT(--(Opportunity!E3:E135="ICDS"),--(Opportunity!C3:C135="SCG"))


"Annie" wrote:

I need help with the following: I am working in a workbook that was created
by someone else. The summary tab is built using Insert Define Name to extract
information from the source tab (OppDB). On a separate tab, I need to
extract the number of products and Factored MRR for specific groups. I've
tried to use what was already built: =DSUM(OppDB,"Factored MRR", P1:P2) and
it's not working P1:P2 houses the specific group.

I've also tried the following:
=SUMPRODUCT(Opportunity!E3:E135="ICDS")*(Opportuni ty!C3:C135="SCG")
=SUM(IF($B$1=Segment, IF(Products=A3, Count)))

With the above formulas I'm attempting to count the number of products
(ICDS) for a specific group (SCG), independently of what has already been
done. Opportunity is the tab that houses the source data.

Any assistance will be greatly appreciated!
--
Thanks In Advance, Annie

"Sheeloo" <Click above to get my email id wrote in message
...


-- converts the values to 1 or 0... so use it when counting... remove it
from one set if you want to SUM

Not correct. -- coerces TRUE or FALSE to 1 or 0, it has no effect on a
numeric value.

--17 is 17

--"abc" is #VALUE"

--TRUE is 9

so although it is not necessary for values being summed, or more accurately
values not being conditionally evaluated, it is not removing them that makes
it summable, SUMPRODUCT is what makes it summable.

Hello Bob,
Thanks for pointing that out...

That is what I wanted to say but... it did not come out right...


"Bob Phillips" wrote:



"Sheeloo" <Click above to get my email id wrote in message
...


-- converts the values to 1 or 0... so use it when counting... remove it
from one set if you want to SUM

Not correct. -- coerces TRUE or FALSE to 1 or 0, it has no effect on a
numeric value.

--17 is 17

--"abc" is #VALUE"

--TRUE is 9

so although it is not necessary for values being summed, or more accurately
values not being conditionally evaluated, it is not removing them that makes
it summable, SUMPRODUCT is what makes it summable.

Hi,

The sumproduct function should work fine - I have tried it. If you are
using the * operator, you need not use --. I worked with the following
example in range J18:K21

ICDS SCG
RTE SCG
ICDS SCG
FGH SCG

=SUMPRODUCT((J18:J21="ICDS")*(K18:K21="SCG")) and the answer is 2

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Annie" wrote in message
...
I need help with the following: I am working in a workbook that was
created
by someone else. The summary tab is built using Insert Define Name to
extract
information from the source tab (OppDB). On a separate tab, I need to
extract the number of products and Factored MRR for specific groups. I've
tried to use what was already built: =DSUM(OppDB,"Factored MRR", P1:P2)
and
it's not working P1:P2 houses the specific group.

I've also tried the following:
=SUMPRODUCT(Opportunity!E3:E135="ICDS")*(Opportuni ty!C3:C135="SCG")
=SUM(IF($B$1=Segment, IF(Products=A3, Count)))

With the above formulas I'm attempting to count the number of products
(ICDS) for a specific group (SCG), independently of what has already been
done. Opportunity is the tab that houses the source data.

Any assistance will be greatly appreciated!
--
Thanks In Advance, Annie