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

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

Try this it works for me:

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

Let me know if it works.

Ed

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

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

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" <Cobbcouk.2bga1f_1153745410.913@excelforum-nospam.com> wrote in
message news:Cobbcouk.2bga1f_1153745410.913@excelforum-nospam.com...
>
> 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
>

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

What was the problem?

--
HTH

Bob Phillips

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

"Cobbcouk" <Cobbcouk.2bhv7q_1153819505.067@excelforum-nospam.com> wrote in
message news:Cobbcouk.2bhv7q_1153819505.067@excelforum-nospam.com...
>
> 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
>