Alienware 17 R4 2017 review
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.
If E7 does not contain a number then to count 1s
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.
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
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)
and so on.
Pretend Col B has no values - only C & D populated. Copying and pasting did not work as expected! My narrative is correct.
Pivot Table click here
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
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
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.
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!
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.
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...
...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.