Joining 2 SUMIF's ???

[Guest]

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]

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

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

 

[Guest]

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?

 

[Ken Wright]

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

 

[Guest]

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

 

[Bob Phillips]

You got this in your previous post.

--

HTH

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