Excel Formulas (Insert Define Name)

[Annie]

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", P12) and
it's not working P12 houses the specific group.

I've also tried the following:
=SUMPRODUCT(Opportunity!E3:E135="ICDS")*(Opportunity!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!

 

[Sheeloo]

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

 

[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]

Use
=DSUM(OppDB,"Factored MRR", P12)
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

 

[Bob Phillips]

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

 

[Sheeloo]

Hello Bob,
Thanks for pointing that out...

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

 

[Ashish Mathur]

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