Excel Multiple Criteria and Stacked Graph (formula issues?)

MrRadioNumbers

New Member
Joined
Mar 29, 2020
Messages
1
Office Version
  1. 2013
Platform
  1. 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

HELP DATA CRUNCH.xlsx
ABCDEFGHIJKLMNOPQ
1SUBURB NAMEGENDERO or WLIST OF ALL SUBURBS & TOWNS# PER SUBURBOWSUBURBtotalOFFICIALMFWEBMF
2WAIKIKIMWWAIKIKI303WAIKIKI303
3WAIKIKIMWWARNBRO4222121
4WAIKIKIFWWANNANUP330
5WARNBROFOWARNBRO422
6WARNBROMOLONG LIST OF COUNT #totaltotal2122
7WARNBROMWEXISTING SUBURB NAMESof timesOW
8WARNBROFW1662 of them!they appearentriesWANNANUP330
9WANNANUPMO2121
10WANNANUPFOwe want to know the number
11WANNANUPMOof males and females who
12entered 'O' hereand 'W' here
13MANUALLYENTEREDDATA
14SUBURB totaltotaltotal
15NAMEx checkOMALEFEMALEWMALEFEMALE
16entriesentries
17
18
19
20
21then use this data to
22create a stacked graph
23
24
25
26
27
28
29
30
help
Cell Formulas
RangeFormula
F2:F3F2=COUNTIF($A$2:$A$11,E2)
G2:G4G2=COUNTIFS($A$2:$A$11, E2, $C$2:$C$11, "=O")
H2:H4H2=COUNTIFS($A$2:$A$11, E2, $C$2:$C$11, "=W")
F4F4=COUNTIF($A$1:$A$11,E4)
K2K2=$F$2
L2L2=$G$2
O2O2=$H$2
M3M3=SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=O","=m"}))
N3N3=SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=O","=F"}))
P3P3=SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=w","=m"}))
Q3Q3=SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=w","=f"}))
K5K5=$F$3
L5L5=$G$3
O5O5=$H$3
M6M6=SUM(COUNTIFS($A$2:$A$11, J5,$B$2:$B11,{"=O","=m"}))
N6N6=SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=O","=f"}))
P6P6=SUM(COUNTIFS($A$2:$A$11, J5,$B$2:$B11,{"=w","=m"}))
Q6Q6=SUM(COUNTIFS($A$2:$A$11, J5,$B$2:$B11,{"=w","=f"}))
K8K8=$F$4
L8L8=$G$4
O8O8=$H$4
M9M9=SUM(COUNTIFS($A$2:$A$11, J8,$B$2:$B11,{"=O","=m"}))
N9N9=SUM(COUNTIFS($A$2:$A$11, J2,$B$2:$B11,{"=O","=f"}))
P9P9=SUM(COUNTIFS($A$2:$A$11, J8,$B$2:$B11,{"=w","=m"}))
Q9Q9=SUM(COUNTIFS($A$2:$A$11, J8,$B$2:$B11,{"=w","=f"}))


capture1.png
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top