Excel Question

  dogbreath1 13:21 25 Jul 2010
Locked

I'm trying to produce a spreadsheet.

Five cells (say c6,c7,d6,d7,e6) containing formulae will be returning numeric integers ranging from 1 and 10 (inc).

I'm looking for a formula to add up the instances of occurrence of each integer, to be displayed in cells a8 to a17 inclusive, similar to below:-

a8 '01' = 0
a9 '02' = 1
a10 '03' = 0
a11 '04' = 1
a12 '05' = 0
a13 '06' = 0
a14 '07' = 1
a15 '08' = 2
a16 '09' = 0
a17 '10' = 0

In the above scenario, a10 (for example) reports that none of the five cells c6,c7,d6,d7,e6 have returned the result '03', whereas a14 reports that one of those cells returned a value '07'.

Hope that makes sense.

  VoG II 15:52 25 Jul 2010

If E6 does not contain a number then to count 1s

=COUNTIF(C6:E7,1)

  VoG II 15:52 25 Jul 2010

I meant

If E7 does not contain a number then to count 1s

  dogbreath1 19:27 25 Jul 2010

Thanks for your reply.

I don't think I explained this adequately.

I need to count the instances of different integers that have been calculated by a set range of cells. If, for example, ten cells each display a number from 0 to 10 dependent upon the result of a calculation they have made, I want to represent this data in terms of how many times each number has appeared.

So, if the output data was a1 = 6, a2 = 4 a3 = 2, a4 = 7, a5 = 6, a6 = 4, a7 = 1, a8 = 0, a9 = 4, a10 = 5, then I'd like this to display as:-

0 had returned 1 time
1 had returned 1 time
2 had returned 1 time
3 had returned 0 times
4 had returned 3 times
5 had returned 1 time
6 had returned 2 times
7 had returned 1 time
8 had returned 0 times
9 had returned 0 times
10 had returned 0 times

Hope that's clearer.

  Zak 22:11 25 Jul 2010

A B C D
6 0 1
4 1 1
2 2 1
7 3 0
6 4 3
4 5 1
1 6 2
0 7 1
4 8 0
5 9 0
10 0

Col A = your integers; Col C = values you are counting and Col D = No, of times
I have started with values in Col A ; Row 2 with headers of A,B,C,D in row 1.

Formula in D2 =COUNTIF($A$2:$A$11,C2)
D3 =COUNTIF($A$2:$A$11,C3)
D4 =COUNTIF($A$2:$A$11,C4)

and so on.

  Zak 22:13 25 Jul 2010

Pretend Col B has no values - only C & D populated. Copying and pasting did not work as expected! My narrative is correct.

  VoG II 07:56 26 Jul 2010

Pivot Table click here

  dogbreath1 13:23 25 Aug 2010

Thanks for trying so far, but I'm still not convinced that I am explaining myself fully.

Column A is a set list of numbers, 5 down to 1 in this particular case.
Column B is a summary of how often those numbers are generated in another part of the worksheet. I've put the 'answers' in column B just to demonstrate what I'm trying to achieve.

Col A Col B
5 5
4 51
3 11
2 21
1 5

Column C has a formula which generates an integer between 1 and 5 over, say, 8 rows.
Column D has a formula which generates an integer with no limiting parameters and is a multiplier for the integer calculated in column C.

Col C Col D
5 2
3 11
4 30
1 5
2 21
4 2
4 19
5 3

The results shown for columns C & D above indicate the incidence of the integer '5' as 5, since it appears 'twice' in the first numerical row and 'three times' in the last numerical row. Similarly, the incidence of the integer '4' is 51, since it appears 'x 30' in the third numerical row, 'x 2' in the sixth numerical row and 'x 19' in the seventh numerical row.

The formulae I need are to generate the results shown in column B, above.

TIA

db

  dogbreath1 13:25 25 Aug 2010

Sadly, the forum software hasn't separated the numbers in columns A & B and C & D respectively as much as I would have liked, but you should still be able to get my drift!

  dogbreath1 13:34 25 Aug 2010

Sorry for hogging this, but I have had a formulae working which, to use this same example, adds the integers in column D IF the adjacent row in column C contains, say, 4. Trouble is, the overall formulae are very long because the IF question has to be asked of every row in the range. Hope that makes sense.

  dogbreath1 14:10 25 Aug 2010

I've been looking at how a pivot table works (per VoG™) and I think that it will do what I need.

How do I use this feature (Excel 97)?

The table needs to fit in the range B17:C22 which gives one row for a header ('length') and five rows for data and where column B is formed of set numbers...

Length
5
4
3
2
1

...and column C reports the incidence of the occurrences of 5, 4, 3, 2 & 1 within a range E62:F83 where column E62:83 contains any combination of results 1 through 5 and adjacent column F62:83 contains integers from 0 to no limit.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Illustrator Sylvain Tegroeg created thousands of intricate line drawings for the mobile game…

Best iPad buying guide 2017

Comment télécharger une application indisponible en France ?