Countif and multiple columns to validate

[Guest]

ok in column T I have a value that needs to be counted if it matches a name.
However it also needs to match a name in column P.

Anyideas on how I could accomplish this?

thanks in advance.

 

[PCLIVE]

Try this:

=SUMPRODUCT(--(T1:T10=FirstCriteria),--(P110=SecondCriteria))

HTH,
Paul

 

[Guest]

=SUM(IF(AND(MATCH(T1,NameField1,0),MATCH(T1,NameField2,0)),1,0))

See if this works... Im thinking that you are matching names so summing the
value 1 is just like counting. This formula if you put the right references
in it should count only the values that match both requirements. LMK if you
have any other questions.

 

[Guest]

Did you test your formula?

=SUM(IF(AND(MATCH(T1,NameField1,0),MATCH(T1,NameField2,0)),1,0))

See if this works... Im thinking that you are matching names so summing the
value 1 is just like counting. This formula if you put the right references
in it should count only the values that match both requirements. LMK if you
have any other questions.

 

[Guest]

Ok I tried both formulas and niether seem to work. Match stops at the first
instance found and the other returns nothing but False even if the criteria
matches. Perhaps it's meant for numbers only?

Any other ideas or should I make a VB macro to handle this?

Also this formula needs to run from a single cell.

Thanks for the help so far folks!

 

[PCLIVE]

Give us an example of your data in columns P and T, in addition to what type
of output you're looking for.

 

[Guest]

ok, heres an example:
P T
hot water
hot milk
hot water
cold water
hot milk
cold milk
cold milk
hot water

So in this example I would want to know how many occurances of hot water
there was and how many times Cold milk occured in this list. The results
would then be on a seperate worksheet with a label above the values found.

Sounds simple enough, but...

 

[Peo Sjoblom]

=SUMPRODUCT(--(P_Range="hot"),--(T_Range="water"))

replace the fictional P and T ranges in my example with your real ranges
i.e. A2:A100 etc

 

[PCLIVE]

Sumproduct should do what you need.

=SUMPRODUCT(--(A2:A9="cold"),--(B2:B9="milk"))

If you need to reference a different sheet:
=SUMPRODUCT(--(Sheet3!A2:A9="cold"),--(Sheet3!B2:B9="milk"))

If you want to references a different workbook:
=SUMPRODUCT(--([Book1.xls]Sheet3!$A$2:$A$9="cold"),--([Book1.xls]Sheet3!$B2:B9="milk"))

Good luck,
Paul

 

[Guest]

I redesigned the sheet a bit to avoid this problem. It's a bit simpler and
strait forward now.

thanks for trying though!