Multiple criteria countif/sumproduct function

C

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

Toppers

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

patele

Try this it works for me:

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

Let me know if it works.

E
 
D

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

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
 
B

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

Cobbcouk

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

Bob Phillips

What was the problem?

--
HTH

Bob Phillips

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