Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF or SUMPRODUCT with multiple criteria | Excel Worksheet Functions | |||
Multiple criteria countif/sumproduct function | Excel Worksheet Functions | |||
COUNTIF or SUMPRODUCT counting multiple criteria | Excel Worksheet Functions | |||
COUNTIF or SUMPRODUCT counting multiple criteria | Excel Worksheet Functions | |||
countif/sumproduct on multiple criteria | Excel Worksheet Functions |