COUNTIF or SUMPRODUCT

[gafern]

I'm seeking help with this scenario...

We create 17 daily files with 10 columns of data. The number of lines or
records in the file varies from day to day, ranging up to perhaps 3,500 per
day. Each file will have a unique file name. One of the column names is
"Event" for there are 6 different events that will appear multiple times:
Event1, Event2, Event3, Event4, Event5 and Event6. Another column is
"Timely" for which there is either Yes or No. There could be 100 Event1,
with 75 Yes and 25 No under Timely, 200 Event2 with 180 Yes and 20 No under
Timely and so on.

I am trying to develop a macro which would include the approproate worksheet
functions whereby I can add a summary sheet to each file showing Event1 had a
quantity of 100, 75 Yes, 25 No for 75% Timely, the same for Event2 and so on.


I know nothing about VB except how to spell it, so writing code is not a
viable option for me.

I am grateful for any advice.

G

 

[Bernie Deitrick]

Use a Pivot Table. Select your data table, then choose Data / Pivot Table..
and click Finish. Drag the Event button to the Rows Field, The Yes/No to
the Column Field, then the Yes/No to the Data Field, and you're done.

HTH,
Bernie
MS Excel MVP

 

[gafern]

Thank you for your response, Bernie.

This outcome is close to my desired result. There are 2 additional elements
I'm still trying to include:

Adding a column that shows the % of "Yes" Timely;
Incoporating this activity in to a macro.

I have to replicate this 17 times a day and am seeking the least
labor-intensive route.

Event No Yes Total % Timely
Event1 9 9 100.00%
Event2 22 89 111 80.18%
Event3 4 2 6 33.33%
Event4 1 6 7 85.71%
Event5 2 2 100.00

Your help, time and expertise is appreciated.

GAF

Use a Pivot Table. Select your data table, then choose Data / Pivot Table..
and click Finish. Drag the Event button to the Rows Field, The Yes/No to
the Column Field, then the Yes/No to the Data Field, and you're done.

HTH,
Bernie
MS Excel MVP

I'm seeking help with this scenario...

We create 17 daily files with 10 columns of data. The number of lines or
records in the file varies from day to day, ranging up to perhaps 3,500 per
day. Each file will have a unique file name. One of the column names is
"Event" for there are 6 different events that will appear multiple times:
Event1, Event2, Event3, Event4, Event5 and Event6. Another column is
"Timely" for which there is either Yes or No. There could be 100 Event1,
with 75 Yes and 25 No under Timely, 200 Event2 with 180 Yes and 20 No under
Timely and so on.

I am trying to develop a macro which would include the approproate worksheet
functions whereby I can add a summary sheet to each file showing Event1 had a
quantity of 100, 75 Yes, 25 No for 75% Timely, the same for Event2 and so on.

I know nothing about VB except how to spell it, so writing code is not a
viable option for me.

I am grateful for any advice.

G

 

[Bernie Deitrick]

GAF,

It's pretty easy. Drag the Yes/No to the data field again, and then right click the new instance and
choose "Field Settings", then "options" and then under 'Show data as' choose "Percent of row"

Also, drag the data button under the row field to change the layout of the data table.

Unfortunately, you will get percents for both Yes and No.... but that is a minor drawback.

HTH,
Bernie
MS Excel MVP