| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with text criteria
Help please.
I have troubles with COUNTIF. I need to count rows with two criteria in different columns both are parts of text. For example: text1- counting text cells- criteria- *text*; text2- C00000005-criteria *5*. My COUNTIF doesnt work. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF with text criteria
Use SUMPRODUCT():
Say we have data like: small cat medium dog large mouse small bird medium ant large cat small dog medium mouse large bird small ant medium cat large dog small mouse medium bird large ant small cat medium dog large mouse small bird medium ant large cat To count the number of rows containing: small cat we can use: =SUMPRODUCT(--(A1:A21="small")*(B1:B21="cat")) Note that this can be expanded to include as many columns as you want. -- Gary''s Student - gsnu200787 "Alexey" wrote: Help please. I have troubles with COUNTIF. I need to count rows with two criteria in different columns both are parts of text. For example: text1- counting text cells- criteria- *text*; text2- C00000005-criteria *5*. My COUNTIF doesnt work. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF with text criteria
Thank you,
But what can we do if we have: very small, extremally small and small cats? and how to cout all small cats? "Gary''s Student" wrote: Use SUMPRODUCT(): Say we have data like: small cat medium dog large mouse small bird medium ant large cat small dog medium mouse large bird small ant medium cat large dog small mouse medium bird large ant small cat medium dog large mouse small bird medium ant large cat To count the number of rows containing: small cat we can use: =SUMPRODUCT(--(A1:A21="small")*(B1:B21="cat")) Note that this can be expanded to include as many columns as you want. -- Gary''s Student - gsnu200787 "Alexey" wrote: Help please. I have troubles with COUNTIF. I need to count rows with two criteria in different columns both are parts of text. For example: text1- counting text cells- criteria- *text*; text2- C00000005-criteria *5*. My COUNTIF doesnt work. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF: 2 criteria: Date Range Column & Text Column | Excel Worksheet Functions | |||
| Using Countif two criteria | Excel Discussion (Misc queries) | |||
| COUNTIF criteria | Excel Discussion (Misc queries) | |||
| Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
| Countif using format criteria....not number criteria? | Excel Worksheet Functions | |||
![Alexey[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
