Couting numbers inside one cell or a range of cells

[PartisanStar]

Let's say my A1 cell looks like this: 1, 1, 1, 1, 1, 5, 3, 1, 1
My A2 cell looks like this: 1, 2, 1, 1

The numbers are separated by a comma.

I want to count how many times a number 1 appears in a cell A1 (7) and in a range of cells A1:A2 (10).

Can you help me out?

 

[Arithos]

AndyPandy27, thanks, works great!

Arithos, you're right and thanks for clearing that out for me! Now it works like it should!

yeah, its an easy thing for us to miss. We need to cover every angle (especially in this case, where his formula was perfect =P )

 

[PartisanStar]

Hey guys, I have another question, hope you'll be able to help me out.

Can I find out how many different numbers (numbers, not rows!) in column A contain only a number 1 in C column?

The anwser for this table should be 18! That is, numbers 2, 5, 8, 10, 11, 12, 14, 15, 16, 20, 21, 24, 25, 26, 27, 28, 31, 35 in A column.

 

[AndyPandy27]

Hi PartisanStar,

Not a very expert or elegant method this, but it works; I did the following:

- Create a Pivot Table of your data
- Add "Novica" to the Row Labels section
- Add "DS vira v besdilu" also to the Row Labels section (after "Novica")
- Go to Pivot Table > Design > Report Layout > Show in Tabular Form
- Remove all subtotals from the Pivot Table
- Go to the Field Settings for the "Novica" field > Layout & Print and tick "Repeat item labels"
--> You should now have a very simple Pivot Table showing a List of the Novica values and next to it a unique list of related "DS vira v besdilu" values

Assuming the "Novica" heading of your Pivot Table is in Cell E1 (i.e. the values of the Pivot Table are in Range E2:F?)...

- In Cell G1 enter the formula: =IF(AND(COUNTIF($E$2:$E$21,E2)=1,F2=1),1,0) --> change the range in bold and underlined to the range in your Pivot Table
- Copy the formula down to the end of the Pivot Table
- In Cell H1 enter the formula: =SUM(G2:G21) --> again, change the range in this formula as required

I hope this helps?

Cheers,

AP

 

[Arithos]

1