SUMPRODUCT or COUNTIF - Multiple Criteria
 

I am working with Excel 2003 - SP3.

From what I understand, if you need to calculate a total number of
items that must meet 2 or more criteria, then COUNTIF is not possible.

I have tried the SUMPRODUCT formula as follows:

=SUMPRODUCT((A1:A100="word1")*(B1:B100="word2")) and amended to
appropriate columns but no matter how I tweak this, my total is always
0. If I amend the * then I get all items matching Criteria 1 and
Criteria 2 but not exclusively.

What I need is a total number that includes only those items meeting
criteria 1 ("WEST" in Column 1) and criteria 2 (Postal Codes starting
with "N").

Is it a format problem with my columns - text vs. number etc?

Any assistance would be appreciated...it will save me creating
worksheets and breaking the info out manually.

Thank you in advance...




--
J Fraggis

SUMPRODUCT or COUNTIF - Multiple Criteria
 

Some soultions have been provided to you in your previous post.
Instead of a new post, you should stick to your old post giving the reason
why the solution provided is not working.

would you post your sample and the result you are after.

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis










"J Fraggis" wrote:


I am working with Excel 2003 - SP3.

From what I understand, if you need to calculate a total number of
items that must meet 2 or more criteria, then COUNTIF is not possible.

I have tried the SUMPRODUCT formula as follows:

=SUMPRODUCT((A1:A100="word1")*(B1:B100="word2")) and amended to
appropriate columns but no matter how I tweak this, my total is always
0. If I amend the * then I get all items matching Criteria 1 and
Criteria 2 but not exclusively.

What I need is a total number that includes only those items meeting
criteria 1 ("WEST" in Column 1) and criteria 2 (Postal Codes starting
with "N").

Is it a format problem with my columns - text vs. number etc?

Any assistance would be appreciated...it will save me creating
worksheets and breaking the info out manually.

Thank you in advance...




--
J Fraggis

SUMPRODUCT or COUNTIF - Multiple Criteria
 

=SUMPRODUCT(--(A1:A100="WEST"),--(LEFT(B1:B100,1)="N"))

--
__________________________________
HTH

Bob

"J Fraggis" wrote in message
...

I am working with Excel 2003 - SP3.

From what I understand, if you need to calculate a total number of
items that must meet 2 or more criteria, then COUNTIF is not possible.

I have tried the SUMPRODUCT formula as follows:

=SUMPRODUCT((A1:A100="word1")*(B1:B100="word2")) and amended to
appropriate columns but no matter how I tweak this, my total is always
0. If I amend the * then I get all items matching Criteria 1 and
Criteria 2 but not exclusively.

What I need is a total number that includes only those items meeting
criteria 1 ("WEST" in Column 1) and criteria 2 (Postal Codes starting
with "N").

Is it a format problem with my columns - text vs. number etc?

Any assistance would be appreciated...it will save me creating
worksheets and breaking the info out manually.

Thank you in advance...




--
J Fraggis