Hello. Within an Excel workbook I have a worksheet that has 30 columns and more than 50,000 rows. Below, I’ve have copied 7 of the columns and 29 of the rows (not including the two column heading rows). Let’s call this worksheet 201617. On another worksheet (also below), which I will call Summary, I need to show the following:
for schools in a given region, the cumulative number of times that TB* (TB1, TB2, TB3, TB4, TB5) was used as an entry (regardless of how many times per incident);
of the 29 incidents listed, the total number of TB* entries was 36
for each school, the number of times TB* was used as an offense entry; and
Examples: Anywhere High = 11entries; Somewhere High = 5 entries
for each school, the number of incidents in which TB* was included as one or more of the offense entries.
Example: Anywhere High = 7 incidents in which TB* was used; Somewhere High = 4 incidents in which TB* was used
I need help on the second and third bullets above. I’ve tried coutnifs (definitely won’t work for the third bullet because it doesn’t work when wanting to count unduplicated info), sumifs, sumproduct, etc., but without luck. And though I’m pretty skilled with Excel, I’m still figuring out VBA, arrays, etc. Please help…and thank you in advance!
NOTE: THIS IS A CORRECTED POST -THE FORMATTING WAS OFF ON THE SECOND CHART (NOT THAT IT'S SO HOT NOW, BUT IT'S THE BEST I COULD DO WITH THE FORMATTING OPTIONS AVAILABLE)
Hello. Within an Excel workbook I have a worksheet that has 30 columns and more than 50,000 rows. Below, I’ve have copied 7 of the columns and 29 of the rows (not including the two column heading rows). Let’s call this worksheet 201617. On another worksheet (also below), which I will call Summary, I need to show the following:
for schools in a given region, the cumulative number of times that TB* (TB1, TB2, TB3, TB4, TB5) was used as an entry (regardless of how many times per incident);
of the 29 incidents listed, the total number of TB* entries was 36
for each school, the number of times TB* was used as an offense entry; and
Examples: Anywhere High = 11entries; Somewhere High = 5 entries
for each school, the number of incidents in which TB* was included as one or more of the offense entries.
Example: Anywhere High = 7 incidents in which TB* was used; Somewhere High = 4 incidents in which TB* was used I need help on the second and third bullets above. I’ve tried coutnifs (definitely won’t work for the third bullet because it doesn’t work when wanting to count unduplicated info), sumifs, sumproduct, etc., but without luck. And though I’m pretty skilled with Excel, I’m still figuring out VBA, arrays, etc. Please help…and thank you in advance!
You didn't say how you generated your Summary table results so far. I created a formula (C4) that creates those totals. Drag the formula down and to the right as needed. The tricky part is determining the TB1, TB2, etc. that is needed. I came up with a tricky OFFSET function to do it, but it really would be easier to just put "TB1" in C1:E1, and format C1 and E1 so that it is hidden.
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself
<tbody>
</tbody>
Given that table, your bullet points 1 and 2 are easy. Total for the region in in B12, and the total by school is in S4 and down.
Bullet point 3 is trickier. I used a MMULT function to act as an OR. It works well, but it may bog your sheet down a lot if you have 30,000 rows. Hope this helps, let us know.
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.
