Multiple criteria countif/sumproduct function

[Cobbcouk]

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

Here is the formula I have used:

=SUMPRODUCT(--('MAIN'!C2:C65534="Phas
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:

 

[Guest]

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

 

[patele]

Try this it works for me:

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

Let me know if it works.

E

 

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

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

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

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

Regards

 

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

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)