ExcelBanter - Countif formula perhaps

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Countif formula perhaps (https://www.excelbanter.com/excel-worksheet-functions/446883-countif-formula-perhaps.html)

[email protected]

Countif formula perhaps

Excel 2010

Whe

Data1 is a named range of nine cells. The values in each cell of Data1 will always be 1 to 9 or a combination of numbers 1 to 9 and each cell will have no duplicates. And each cell could have just 1 number or perhaps three or all 9.

P1 is a cell with a Data Validation drop down list of numbers 1 to 9.

In a separate cell I have used =COUNTIF(Data1,P1) to count the number times the P1 value occurs in Data1. It does not work.

Can you suggest a formula to do this?

Thanks.
Regards,
Howard

Living the Dream

Countif formula perhaps

On 19/08/2012 10:36 AM, wrote:
Excel 2010

Whe

Data1 is a named range of nine cells. The values in each cell of Data1 will always be 1 to 9 or a combination of numbers 1 to 9 and each cell will have no duplicates. And each cell could have just 1 number or perhaps three or all 9.

P1 is a cell with a Data Validation drop down list of numbers 1 to 9.

In a separate cell I have used =COUNTIF(Data1,P1) to count the number times the P1 value occurs in Data1. It does not work.

Can you suggest a formula to do this?

Thanks.
Regards,
Howard

Hi Howard

Your formula works for me using 2010, given that the Data1 is purely
digits 1 to 9. I changed it around and included 4 x 6's and a variety of
different combinations and it still worked for me.

What type of combination are you referring to, and when you say, "Not
Working", can you give an example of what the cell is displaying, if
anything.

HTH
Mick.

[email protected]

Countif formula perhaps

On Saturday, August 18, 2012 8:27:50 PM UTC-7, Living the Dream wrote:
On 19/08/2012 10:36 AM, wrote:

Excel 2010

Whe

Data1 is a named range of nine cells. The values in each cell of Data1 will always be 1 to 9 or a combination of numbers 1 to 9 and each cell will have no duplicates. And each cell could have just 1 number or perhaps three or all 9.

P1 is a cell with a Data Validation drop down list of numbers 1 to 9.

In a separate cell I have used =COUNTIF(Data1,P1) to count the number times the P1 value occurs in Data1. It does not work.

Can you suggest a formula to do this?

Thanks.

Regards,

Howard

Hi Howard

Your formula works for me using 2010, given that the Data1 is purely

digits 1 to 9. I changed it around and included 4 x 6's and a variety of

different combinations and it still worked for me.

What type of combination are you referring to, and when you say, "Not

Working", can you give an example of what the cell is displaying, if

anything.

HTH

Mick.

Hi Mick,
Thanks for the response

Here is a sample of entries for Data1. With 4 in the drop down cell P1 I would expect to return the value 5. =COUNTIF(Data1,P1) returns 0 (zero).

123456789 456 234
123 4 479
5 149 289

Howard

Spencer101

1 Attachment(s)

Quote:

Originally Posted by (Post 1604732)

Have a look at the attachment. Is this what you mean?

I cannot take credit for the formula. I pinched it from another forum and adapted it to your needs.

Worth pointing out that in your example above, there are not 5 instances of 4, but rather 6 :)

Living the Dream

Countif formula perhaps

Hi Howard

Well that paints a different picture in that you are wanting to count
specifically, the matching numerals within a group of numbers.

This would require the combination use of possibly MID, LEN, MATCH,
INDIRECT, essentially a fairly complex formula of which is beyond my
level of expertise I'm afraid.

Good luck

Mick.

plinius

Countif formula perhaps

Il 19/08/2012 06:17, ha scritto:

[....]

Hi Mick,
Thanks for the response

Here is a sample of entries for Data1. With 4 in the drop down cell P1 I would expect to return the value 5. =COUNTIF(Data1,P1) returns 0 (zero).

123456789 456 234
123 4 479
5 149 289

Howard

Try
=SUMPRODUCT(1*NOT(ISERROR(FIND(P1,Data1))))

Hi, E.

Claus Busch

Countif formula perhaps

Hi Howard,

Am Sat, 18 Aug 2012 21:17:42 -0700 (PDT) schrieb :

Here is a sample of entries for Data1. With 4 in the drop down cell P1 I would expect to return the value 5. =COUNTIF(Data1,P1) returns 0 (zero).

123456789 456 234
123 4 479
5 149 289

try:
=COUNT(SEARCH(P1,Data))
and enter the array formula with CTRL+Shift+Enter

In your example I see 6 times the 4

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[email protected]

Countif formula perhaps

On Saturday, August 18, 2012 5:36:59 PM UTC-7, (unknown) wrote:
Excel 2010

Whe

Data1 is a named range of nine cells. The values in each cell of Data1 will always be 1 to 9 or a combination of numbers 1 to 9 and each cell will have no duplicates. And each cell could have just 1 number or perhaps three or all 9.

P1 is a cell with a Data Validation drop down list of numbers 1 to 9.

In a separate cell I have used =COUNTIF(Data1,P1) to count the number times the P1 value occurs in Data1. It does not work.

Can you suggest a formula to do this?

Thanks.

Regards,

Howard

On Saturday, August 18, 2012 5:36:59 PM UTC-7, (unknown) wrote:
Excel 2010

Whe

Data1 is a named range of nine cells. The values in each cell of Data1 will always be 1 to 9 or a combination of numbers 1 to 9 and each cell will have no duplicates. And each cell could have just 1 number or perhaps three or all 9.

P1 is a cell with a Data Validation drop down list of numbers 1 to 9.

In a separate cell I have used =COUNTIF(Data1,P1) to count the number times the P1 value occurs in Data1. It does not work.

Can you suggest a formula to do this?

Thanks.

Regards,

Howard

Whoops, six is the correct numbrer, I missed one!

Thanks everyone, I'll give it a go.

Regards,
Howard

[email protected]

Countif formula perhaps

All times are GMT +1. The time now is 04:28 PM.