Multiple criteria countif/sumproduct function
 

Hi,
I am trying to count criteria from two different columns and have the
answer appear on a different worksheet.

Here is the formula I have used:

=SUMPRODUCT(--('MAIN'!C2:C65534="Phase
2"),--('MAIN'!B2:B65534="Downstairs"))

I cannot get it to work on another worksheet within the same workbook!
Am I trying the impossible?

Any help would be appreciated:)


--
Cobbcouk
------------------------------------------------------------------------
Cobbcouk's Profile: http://www.excelforum.com/member.php...o&userid=32143
View this thread: http://www.excelforum.com/showthread...hreadid=564241

Multiple criteria countif/sumproduct function
 

What doesn't work? What results do you get? The formula is perfectly valid
and my test worked O.K.

"Cobbcouk" wrote:


Hi,
I am trying to count criteria from two different columns and have the
answer appear on a different worksheet.

Here is the formula I have used:

=SUMPRODUCT(--('MAIN'!C2:C65534="Phase
2"),--('MAIN'!B2:B65534="Downstairs"))

I cannot get it to work on another worksheet within the same workbook!
Am I trying the impossible?

Any help would be appreciated:)


--
Cobbcouk
------------------------------------------------------------------------
Cobbcouk's Profile: http://www.excelforum.com/member.php...o&userid=32143
View this thread: http://www.excelforum.com/showthread...hreadid=564241

Multiple criteria countif/sumproduct function
 

Try this it works for me:

=SUMPRODUCT((Main!C1:C10="Phase 2")*(Main!B1:B10="Downstairs"))

Let me know if it works.

Ed


--
patele
------------------------------------------------------------------------
patele's Profile: http://www.excelforum.com/member.php...o&userid=35849
View this thread: http://www.excelforum.com/showthread...hreadid=564241

Multiple criteria countif/sumproduct function
 

You may want to copy the formula from the formula bar and past it into your
followup message if you don't get it working.

Cobbcouk wrote:

Hi,
I am trying to count criteria from two different columns and have the
answer appear on a different worksheet.

Here is the formula I have used:

=SUMPRODUCT(--('MAIN'!C2:C65534="Phase
2"),--('MAIN'!B2:B65534="Downstairs"))

I cannot get it to work on another worksheet within the same workbook!
Am I trying the impossible?

Any help would be appreciated:)

--
Cobbcouk
------------------------------------------------------------------------
Cobbcouk's Profile: http://www.excelforum.com/member.php...o&userid=32143
View this thread: http://www.excelforum.com/showthread...hreadid=564241

--

Dave Peterson

Multiple criteria countif/sumproduct function
 

Hi,
Thanks for the quick replies. The formula works correctly in the same
worksheet but not in anothe. I have just created a new worksheet and
it works in that one; but not in the original one I created for the
formula to work in.

I don't know bloomin Excel!!!!!

Thanks for the help ill just hhave to copy the sheet info across and
hope for the best.

Regards

G


--
Cobbcouk
------------------------------------------------------------------------
Cobbcouk's Profile: http://www.excelforum.com/member.php...o&userid=32143
View this thread: http://www.excelforum.com/showthread...hreadid=564241

Multiple criteria countif/sumproduct function
 

You probably need to update the sheet reference in the formula, the Main!
part, to the appropriate sheet.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Cobbcouk" wrote in
message ...

Hi,
Thanks for the quick replies. The formula works correctly in the same
worksheet but not in anothe. I have just created a new worksheet and
it works in that one; but not in the original one I created for the
formula to work in.

I don't know bloomin Excel!!!!!

Thanks for the help ill just hhave to copy the sheet info across and
hope for the best.

Regards

G


--
Cobbcouk
------------------------------------------------------------------------
Cobbcouk's Profile:
http://www.excelforum.com/member.php...o&userid=32143
View this thread: http://www.excelforum.com/showthread...hreadid=564241

Multiple criteria countif/sumproduct function
 

Thanks guys for your help. All works fine now. Another battle won in
the Excel forums:) :) :) :) :) :) :) :) :)


--
Cobbcouk
------------------------------------------------------------------------
Cobbcouk's Profile: http://www.excelforum.com/member.php...o&userid=32143
View this thread: http://www.excelforum.com/showthread...hreadid=564241

Multiple criteria countif/sumproduct function
 

What was the problem?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Cobbcouk" wrote in
message ...

Thanks guys for your help. All works fine now. Another battle won in
the Excel forums:) :) :) :) :) :) :) :) :)


--
Cobbcouk
------------------------------------------------------------------------
Cobbcouk's Profile:
http://www.excelforum.com/member.php...o&userid=32143
View this thread: http://www.excelforum.com/showthread...hreadid=564241