How can I join the following SUMIF's in to a single formula? Please help my
heads about to blow up :)

=SUMIF(Country range, country criteria,revenue range)
=SUMIF(Opportuntity range, Opp criterion, revenue range)

Need to apply both criterion to the SUM revenue range. How do I do this??

=sumproduct(--(countryrng=countrycriteria),--(opprng=oppcriteria),revrng)

=sumproduct() likes to work with numbers. the -- stuff converts trues and
falses to 1/0's.



AC-H wrote:

How can I join the following SUMIF's in to a single formula? Please help my
heads about to blow up :)

=SUMIF(Country range, country criteria,revenue range)
=SUMIF(Opportuntity range, Opp criterion, revenue range)

Need to apply both criterion to the SUM revenue range. How do I do this??

--

Dave Peterson

Thanks for this Dave. However is returning a #NUM! error

This is my formula below. Can you see where I am going wrong and how I can
fix it?

'=SUMPRODUCT(--('My-Sales_Data_Sheet'!AN:AN="QOCWLD"),--('My-Sales_Data_Sheet'!$K:$K=A33),'My-Sales_Data_Sheet'!$Z:$Z)

Does this mean that I can't use this to look up Country names and other text?




"Dave Peterson" wrote:

=sumproduct(--(countryrng=countrycriteria),--(opprng=oppcriteria),revrng)

=sumproduct() likes to work with numbers. the -- stuff converts trues and
falses to 1/0's.



AC-H wrote:

How can I join the following SUMIF's in to a single formula? Please help my
heads about to blow up :)

=SUMIF(Country range, country criteria,revenue range)
=SUMIF(Opportuntity range, Opp criterion, revenue range)

Need to apply both criterion to the SUM revenue range. How do I do this??

--

Dave Peterson

You can't use full column references with SUMPRODUCT. You need to put in
row values, eg:-

=SUMPRODUCT(--('My-Sales_Data_Sheet'!AN1:AN1000="QOCWLD"),--('My-Sales_Data_Sheet'!$K1:$K1000=A33),'My-Sales_Data_Sheet'!$Z1:$Z1000)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"AC-H" wrote in message
...
Thanks for this Dave. However is returning a #NUM! error

This is my formula below. Can you see where I am going wrong and how I
can
fix it?

'=SUMPRODUCT(--('My-Sales_Data_Sheet'!AN:AN="QOCWLD"),--('My-Sales_Data_Sheet'!$K:$K=A33),'My-Sales_Data_Sheet'!$Z:$Z)

Does this mean that I can't use this to look up Country names and other
text?




"Dave Peterson" wrote:

=sumproduct(--(countryrng=countrycriteria),--(opprng=oppcriteria),revrng)

=sumproduct() likes to work with numbers. the -- stuff converts trues
and
falses to 1/0's.



AC-H wrote:

How can I join the following SUMIF's in to a single formula? Please
help my
heads about to blow up :)

=SUMIF(Country range, country criteria,revenue range)
=SUMIF(Opportuntity range, Opp criterion, revenue range)

Need to apply both criterion to the SUM revenue range. How do I do
this??

--

Dave Peterson

Hi,

Dave's formula is perfect.
Specify the ranges explicitly in your formula; i.e., change AN:AN, $K:$K,
and $Z:$Z to their actual ranges, such as AN2:AN101, $K$2:$K$101, and
$Z$2:$Z$101 (please note that the ranges should be of the same size).

Regards,
B. R. Ramachandran

"AC-H" wrote:

Thanks for this Dave. However is returning a #NUM! error

This is my formula below. Can you see where I am going wrong and how I can
fix it?

'=SUMPRODUCT(--('My-Sales_Data_Sheet'!AN:AN="QOCWLD"),--('My-Sales_Data_Sheet'!$K:$K=A33),'My-Sales_Data_Sheet'!$Z:$Z)

Does this mean that I can't use this to look up Country names and other text?




"Dave Peterson" wrote:

=sumproduct(--(countryrng=countrycriteria),--(opprng=oppcriteria),revrng)

=sumproduct() likes to work with numbers. the -- stuff converts trues and
falses to 1/0's.



AC-H wrote:

How can I join the following SUMIF's in to a single formula? Please help my
heads about to blow up :)

=SUMIF(Country range, country criteria,revenue range)
=SUMIF(Opportuntity range, Opp criterion, revenue range)

Need to apply both criterion to the SUM revenue range. How do I do this??

--

Dave Peterson

You got this in your previous post.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"AC-H" wrote in message
...
How can I join the following SUMIF's in to a single formula? Please help
my
heads about to blow up :)

=SUMIF(Country range, country criteria,revenue range)
=SUMIF(Opportuntity range, Opp criterion, revenue range)

Need to apply both criterion to the SUM revenue range. How do I do this??