Conditional Sums Using Multiple Criteria Error 1

[aprunkard]

I am trying to create a formula that sums the Revenue Column (Range Named "Revenue"), only if the Named Ranges Client="CR" AND Campaign="PCR". I have tried:

{=SUM((Client="CR")*(Campaign="PCR")*Revenue)}
I get a #NUM! error

I have tried not using an array and I get a result but it's not even close. It's so far off that I can't even figure out where it's getting that result from.

What am I missing?

 

[dallen43]

Try:

=SUMPRODUCT(Revenue*(Client="CR")*(Campain="PCR"))

 

[aprunkard]

I get the #NUM! error, even when I try the array.
Would it make a different if my named ranges are on a different sheet from where I am trying to put the formula?

 

[dallen43]

I have setup sheet1 with 3 columns: Revenue, Client and Campain.

I have defined the name ranges as following:

Campain=Sheet1!$C$2:$C$13
Client=Sheet1!$B$2:$B$13
Revenue=Sheet1!$A$2:$A$13

Notice that they all contain the same number of rows.

Then I have placed the formula on Sheet2:

=SUMPRODUCT(Revenue*(Client="CR")*(Campain="PCR"))

It works because the ranges contain the Worksheet information.

I'm using Excel 2003.

If this doesn't work for you, please check your ranges to be sure you are using the correct names and that the ranges contain the worksheet.

Hope this helps.

 

[GlennUK]

Hi aprunkard,

what are the precise definitions of your names please? ( If they are entire columns, make them shorter by one row .. e.g. $A$1:$A$65535 instead of $A:$A )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[aprunkard]

Thanks, Glenn.
That worked!

 

[Hasit]

aprunkard, suggest you give Glenn a star because he resolved the issue for you, by identifying something that others did not figure out.