Multiple criteria countif/sumproduct function

[Cobbcouk]

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

[Toppers]

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

[patele]

Try this it works for me:

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

Let me know if it works.

Ed

[Dave Peterson]

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

[Cobbcouk]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Cobbcouk]

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

[Bob Phillips]

What was the problem?

--
HTH

Bob Phillips

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

"Cobbcouk" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>