NotAsGoodAsIThoughtIWas
New Member
- Joined
- Jul 28, 2010
- Messages
- 10
I am using Excel 2010.
I have a spreadsheet where column A is for Quarter, column B is for Employee, and columns C-R are for Codes (Code1; Code2; Code3; etc. through Code15). One line might have no values in the codes columns, another might have values in only Code1, another might have values in Code1 and Code2, and another might have values in all 15 columns.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
<o></o>
A B C D E F --> R<o></o>
1 Qtr Emp Code01 Code02 Code03 Code04 --> to Code15 <o></o>
2 2012.1 Liz CER02 INS12 WKH15<o></o>
3 2012.2 Jim PIN55 WKH12 WKH19 WKH23<o></o>
4 2012.2 Jon <o></o>
5 2012.2 Jim WKH15 WKH23<o></o>
6 2012.2 Jon PIN55 WKH15 WKH12 CER08
<o></o>
The worksheet is named “ALLAUDITS” and the named ranges are as follows: <o></o>
<o></o>
Quarter: =offset(allaudits!$A$1,0,0,counta(allaudits!$A:$A),1)<o></o>
Employee: =offset(allaudits!$B$1,0,0,counta(allaudits!$B:$B),1)<o></o>
CodeData: =offset(allaudits!$C$1,0,0,counta(allaudits!$C:$C),15)<o></o>
<o></o>
On another worksheet I need to be able to count how many times WKH15 appears in the CodeData range within a certain Quarter. I tried
=countifs(Quarter,"2012.2",CodeData,"WKH15") but that didn't work, and from what I can tell in Excel Help, it's because the ranges are different sizes. I have over 6500 rows, so I'm trying to avoid having to name all 15 columns and then do a sumproduct on all of them, such as
=sumproduct((Quarter="2012.2")*((Code1="WKH15")+(Code2="WKH15")+.......)))
Calculation would take forever.
I've been googling for three days and scouring this and other message boards, but cannot find anything that will work. In an Excelutopia, I could just create a pivot table based on the "allaudits" worksheet, but I can't figure out how to get the actual codes as the row headers (instead of Code1, Code2, Code3, etc.). So if you know how to make THAT happen instead, I'd be ecstatic!!
I have a spreadsheet where column A is for Quarter, column B is for Employee, and columns C-R are for Codes (Code1; Code2; Code3; etc. through Code15). One line might have no values in the codes columns, another might have values in only Code1, another might have values in Code1 and Code2, and another might have values in all 15 columns.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o><o
></o>A B C D E F --> R<o
></o>1 Qtr Emp Code01 Code02 Code03 Code04 --> to Code15 <o
></o>2 2012.1 Liz CER02 INS12 WKH15<o
></o>3 2012.2 Jim PIN55 WKH12 WKH19 WKH23<o
></o>4 2012.2 Jon <o
></o>5 2012.2 Jim WKH15 WKH23<o
></o>6 2012.2 Jon PIN55 WKH15 WKH12 CER08
<o
></o>The worksheet is named “ALLAUDITS” and the named ranges are as follows: <o
></o><o
></o>Quarter: =offset(allaudits!$A$1,0,0,counta(allaudits!$A:$A),1)<o
></o>Employee: =offset(allaudits!$B$1,0,0,counta(allaudits!$B:$B),1)<o
></o>CodeData: =offset(allaudits!$C$1,0,0,counta(allaudits!$C:$C),15)<o
></o><o
></o>On another worksheet I need to be able to count how many times WKH15 appears in the CodeData range within a certain Quarter. I tried
=countifs(Quarter,"2012.2",CodeData,"WKH15") but that didn't work, and from what I can tell in Excel Help, it's because the ranges are different sizes. I have over 6500 rows, so I'm trying to avoid having to name all 15 columns and then do a sumproduct on all of them, such as
=sumproduct((Quarter="2012.2")*((Code1="WKH15")+(Code2="WKH15")+.......)))
Calculation would take forever.
I've been googling for three days and scouring this and other message boards, but cannot find anything that will work. In an Excelutopia, I could just create a pivot table based on the "allaudits" worksheet, but I can't figure out how to get the actual codes as the row headers (instead of Code1, Code2, Code3, etc.). So if you know how to make THAT happen instead, I'd be ecstatic!!
