MrRadioNumbers
New Member
- Joined
- Mar 29, 2020
- Messages
- 1
- Office Version
- 2013
- Platform
- Windows
Hi everyone, long time lurker and a semi-newb to Excel - I have been using the forums for years, and you always seem to have the perfect answer to fit an issue I am having...
However, this time I am stuck... hopefully you can help.
I am trying to generate a stacked graph from data generated from the manual input of entries to a competition that are either "O"fficial or "W"eb entries, cross checking that against suburbs people are from and their gender. I am open to other alternatives to get this info across, if you have any suggestions!
I suspect that my issue is with 2 sets of formulas... both countifs, but I might be going about it wrong... =SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=O","=m"})) and the alternate version =SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=w","=m"}))
I have included the sheet as a pic at the bottom of the post, and the XL2bb capture (sorry if its too much info), and hopefully I have this all right for the forum.
Thanks in advance for assisting.
Cheers.
MrRadioNumbers
However, this time I am stuck... hopefully you can help.
I am trying to generate a stacked graph from data generated from the manual input of entries to a competition that are either "O"fficial or "W"eb entries, cross checking that against suburbs people are from and their gender. I am open to other alternatives to get this info across, if you have any suggestions!
I suspect that my issue is with 2 sets of formulas... both countifs, but I might be going about it wrong... =SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=O","=m"})) and the alternate version =SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=w","=m"}))
I have included the sheet as a pic at the bottom of the post, and the XL2bb capture (sorry if its too much info), and hopefully I have this all right for the forum.
Thanks in advance for assisting.
Cheers.
MrRadioNumbers
HELP DATA CRUNCH.xlsx | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
1 | SUBURB NAME | GENDER | O or W | LIST OF ALL SUBURBS & TOWNS | # PER SUBURB | O | W | SUBURB | total | OFFICIAL | M | F | WEB | M | F | ||||
2 | WAIKIKI | M | W | WAIKIKI | 3 | 0 | 3 | WAIKIKI | 3 | 0 | 3 | ||||||||
3 | WAIKIKI | M | W | WARNBRO | 4 | 2 | 2 | 2 | 1 | 2 | 1 | ||||||||
4 | WAIKIKI | F | W | WANNANUP | 3 | 3 | 0 | ||||||||||||
5 | WARNBRO | F | O | WARNBRO | 4 | 2 | 2 | ||||||||||||
6 | WARNBRO | M | O | LONG LIST OF | COUNT # | total | total | 2 | 1 | 2 | 2 | ||||||||
7 | WARNBRO | M | W | EXISTING SUBURB NAMES | of times | O | W | ||||||||||||
8 | WARNBRO | F | W | 1662 of them! | they appear | entries | WANNANUP | 3 | 3 | 0 | |||||||||
9 | WANNANUP | M | O | 2 | 1 | 2 | 1 | ||||||||||||
10 | WANNANUP | F | O | we want to know the number | |||||||||||||||
11 | WANNANUP | M | O | of males and females who | |||||||||||||||
12 | entered 'O' here | and 'W' here | |||||||||||||||||
13 | MANUALLY | ENTERED | DATA | ||||||||||||||||
14 | SUBURB | total | total | total | |||||||||||||||
15 | NAME | x check | O | MALE | FEMALE | W | MALE | FEMALE | |||||||||||
16 | entries | entries | |||||||||||||||||
17 | |||||||||||||||||||
18 | |||||||||||||||||||
19 | |||||||||||||||||||
20 | |||||||||||||||||||
21 | then use this data to | ||||||||||||||||||
22 | create a stacked graph | ||||||||||||||||||
23 | |||||||||||||||||||
24 | |||||||||||||||||||
25 | |||||||||||||||||||
26 | |||||||||||||||||||
27 | |||||||||||||||||||
28 | |||||||||||||||||||
29 | |||||||||||||||||||
30 | |||||||||||||||||||
help |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F3 | F2 | =COUNTIF($A$2:$A$11,E2) |
G2:G4 | G2 | =COUNTIFS($A$2:$A$11, E2, $C$2:$C$11, "=O") |
H2:H4 | H2 | =COUNTIFS($A$2:$A$11, E2, $C$2:$C$11, "=W") |
F4 | F4 | =COUNTIF($A$1:$A$11,E4) |
K2 | K2 | =$F$2 |
L2 | L2 | =$G$2 |
O2 | O2 | =$H$2 |
M3 | M3 | =SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=O","=m"})) |
N3 | N3 | =SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=O","=F"})) |
P3 | P3 | =SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=w","=m"})) |
Q3 | Q3 | =SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=w","=f"})) |
K5 | K5 | =$F$3 |
L5 | L5 | =$G$3 |
O5 | O5 | =$H$3 |
M6 | M6 | =SUM(COUNTIFS($A$2:$A$11, J5,$B$2:$B11,{"=O","=m"})) |
N6 | N6 | =SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=O","=f"})) |
P6 | P6 | =SUM(COUNTIFS($A$2:$A$11, J5,$B$2:$B11,{"=w","=m"})) |
Q6 | Q6 | =SUM(COUNTIFS($A$2:$A$11, J5,$B$2:$B11,{"=w","=f"})) |
K8 | K8 | =$F$4 |
L8 | L8 | =$G$4 |
O8 | O8 | =$H$4 |
M9 | M9 | =SUM(COUNTIFS($A$2:$A$11, J8,$B$2:$B11,{"=O","=m"})) |
N9 | N9 | =SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=O","=f"})) |
P9 | P9 | =SUM(COUNTIFS($A$2:$A$11, J8,$B$2:$B11,{"=w","=m"})) |
Q9 | Q9 | =SUM(COUNTIFS($A$2:$A$11, J8,$B$2:$B11,{"=w","=f"})) |