Loss1003
01-24-2014, 10:11 AM
Hello and Happy Friday,
Is it possible to perform a count if or countifs statement from data on one worksheet based on 3 data validation combo boxes from another?
I’m currently utilizing a few sheets in one workbook to perform a combination of IF(AND & Countifs Statements formulas to calculate based on 2 criteria.
The two criteria are loaded into two data validation boxes on my Sector Chart Worksheet. Based on the choices selected in the boxes, the formulas in the Sector Chart and Sector Worksheets will calculate properly. However I’ve been unsuccessful in adjusting the formulas to include a 3rd combo box.
The problem in part is because I have “Include All” in each of the combo boxes, so the data can properly reflect to pull no matter what text is included in the column(s) that need to be counted. Data (Worksheet)Sector Chart (Worksheet)Sector (Worksheet)I currently have these two data validation boxes on sector chart
Year: 2013
Sector: Include All
I’d like to add a Vendor box
Year: 2013
Sector: Include All
Vendor: Vertex
I've listed some of my current code:
Sector Chart Worksheet
=IF(AND('Policy Num List'!I$2="Include All",'Policy Num List'!I$23="Include All"),COUNTIFS(DATA!H$2:H$2395,"January",DATA!X$2:X$2395,"X",DATA!AJ$2:AJ$2395,"01"),Sector!D2)
Sector Worksheet
If user selects includes all for just Sector Box
=IF('Policy Num List'!I2="Include All",COUNTIFS(DATA!A$2:A$2395,'Policy Num List'!I23,DATA!H$2:H$2395,"January",DATA!X$2:X$2395,"X",DATA!AJ$2:AJ$2395,"01"),E2)
If user selects includes all for just Year Box
=IF('Policy Num List'!I23="Include All",COUNTIFS(DATA!F$2:F$2395,'Policy Num List'!I2,DATA!H$2:H$2395,"January",DATA!X$2:X$2395,"X",DATA!AJ$2:AJ$2395,"01"),F2)
If no Include All box is present in the Year or Sector Box
=COUNTIFS(DATA!A$2:A$2395,'Policy Num List'!I23,DATA!F$2:F$2395,'Policy Num List'!I2,DATA!H$2:H$2395,"January",DATA!X$2:X$2395,"X",DATA!AJ$2:AJ$2395,"01")
Any help is appreciated. I'm unsure how to attach the worksheet.
Is it possible to perform a count if or countifs statement from data on one worksheet based on 3 data validation combo boxes from another?
I’m currently utilizing a few sheets in one workbook to perform a combination of IF(AND & Countifs Statements formulas to calculate based on 2 criteria.
The two criteria are loaded into two data validation boxes on my Sector Chart Worksheet. Based on the choices selected in the boxes, the formulas in the Sector Chart and Sector Worksheets will calculate properly. However I’ve been unsuccessful in adjusting the formulas to include a 3rd combo box.
The problem in part is because I have “Include All” in each of the combo boxes, so the data can properly reflect to pull no matter what text is included in the column(s) that need to be counted. Data (Worksheet)Sector Chart (Worksheet)Sector (Worksheet)I currently have these two data validation boxes on sector chart
Year: 2013
Sector: Include All
I’d like to add a Vendor box
Year: 2013
Sector: Include All
Vendor: Vertex
I've listed some of my current code:
Sector Chart Worksheet
=IF(AND('Policy Num List'!I$2="Include All",'Policy Num List'!I$23="Include All"),COUNTIFS(DATA!H$2:H$2395,"January",DATA!X$2:X$2395,"X",DATA!AJ$2:AJ$2395,"01"),Sector!D2)
Sector Worksheet
If user selects includes all for just Sector Box
=IF('Policy Num List'!I2="Include All",COUNTIFS(DATA!A$2:A$2395,'Policy Num List'!I23,DATA!H$2:H$2395,"January",DATA!X$2:X$2395,"X",DATA!AJ$2:AJ$2395,"01"),E2)
If user selects includes all for just Year Box
=IF('Policy Num List'!I23="Include All",COUNTIFS(DATA!F$2:F$2395,'Policy Num List'!I2,DATA!H$2:H$2395,"January",DATA!X$2:X$2395,"X",DATA!AJ$2:AJ$2395,"01"),F2)
If no Include All box is present in the Year or Sector Box
=COUNTIFS(DATA!A$2:A$2395,'Policy Num List'!I23,DATA!F$2:F$2395,'Policy Num List'!I2,DATA!H$2:H$2395,"January",DATA!X$2:X$2395,"X",DATA!AJ$2:AJ$2395,"01")
Any help is appreciated. I'm unsure how to attach the worksheet.