| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF or SUMPRODUCT?
First of all, I would like to say I have found a lot of questions & answers
that have become useful to me in my day to day excel experience. Thanks to all the posters and the intelligent folks that have the answers! My question is... I have 2 columns of data, column A A1:A100 =RC44, column B (need to be less < than 74) count as 1 EXAMPLE: Column A Column B RC44 60 RC44 77 RC52 14 RC60 58 RC44 72 If coumn A = RC44 & column B is less than 74 should =1 (or example above is= 2) Can anyone provide any help? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF or SUMPRODUCT?
=sumproduct(--(a1:a100="rc44"),--(b1:b100<74))
Adjust the range, but you can't use the whole column. Scott wrote: First of all, I would like to say I have found a lot of questions & answers that have become useful to me in my day to day excel experience. Thanks to all the posters and the intelligent folks that have the answers! My question is... I have 2 columns of data, column A A1:A100 =RC44, column B (need to be less < than 74) count as 1 EXAMPLE: Column A Column B RC44 60 RC44 77 RC52 14 RC60 58 RC44 72 If coumn A = RC44 & column B is less than 74 should =1 (or example above is= 2) Can anyone provide any help? -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF or SUMPRODUCT?
=SUMPRODUCT(--(A1:A100="RC44"),--(B1:B100<74))
-- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email) "Scott" wrote in message ... First of all, I would like to say I have found a lot of questions & answers that have become useful to me in my day to day excel experience. Thanks to all the posters and the intelligent folks that have the answers! My question is... I have 2 columns of data, column A A1:A100 =RC44, column B (need to be less < than 74) count as 1 EXAMPLE: Column A Column B RC44 60 RC44 77 RC52 14 RC60 58 RC44 72 If coumn A = RC44 & column B is less than 74 should =1 (or example above is= 2) Can anyone provide any help? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF or SUMPRODUCT?
Thanks for the information, works perfectly!!!
"Peo Sjoblom" wrote: =SUMPRODUCT(--(A1:A100="RC44"),--(B1:B100<74)) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email) "Scott" wrote in message ... First of all, I would like to say I have found a lot of questions & answers that have become useful to me in my day to day excel experience. Thanks to all the posters and the intelligent folks that have the answers! My question is... I have 2 columns of data, column A A1:A100 =RC44, column B (need to be less < than 74) count as 1 EXAMPLE: Column A Column B RC44 60 RC44 77 RC52 14 RC60 58 RC44 72 If coumn A = RC44 & column B is less than 74 should =1 (or example above is= 2) Can anyone provide any help? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF or SUMPRODUCT?
Try
=SUMPRODUCT((A2:A6="RC44")*(B2:B6<74)) if the value comes as #value click F2 and hit control+shift+enter based on your five sets of values(a2 to a6 and b2 to b6). check whether there are any nonprintable characters in your cell entreis. "Scott" wrote in message ... First of all, I would like to say I have found a lot of questions & answers that have become useful to me in my day to day excel experience. Thanks to all the posters and the intelligent folks that have the answers! My question is... I have 2 columns of data, column A A1:A100 =RC44, column B (need to be less < than 74) count as 1 EXAMPLE: Column A Column B RC44 60 RC44 77 RC52 14 RC60 58 RC44 72 If coumn A = RC44 & column B is less than 74 should =1 (or example above is= 2) Can anyone provide any help? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct vs. countif | Excel Discussion (Misc queries) | |||
| Sumproduct, If, Sumif, Countif, Match?? | Excel Discussion (Misc queries) | |||
| countif more than one sheet/tab and sumproduct | Excel Worksheet Functions | |||
| countif, sumproduct | New Users to Excel | |||
| Using COUNTIF with 2 criteria - SUMPRODUCT? | Excel Worksheet Functions | |||
Linear Mode
