Need help constructing a formula to count cells and create a chart from the data not sure.

[MilsonHQ]

I'm not sure what to search for, I have had a look around the site and not seeing anything that specifically meets my needs, so basically what I have is as follows.

I have several individual sheets in a workbook each sheet corresponds to a day of the week Mon-Fri

On each sheet there is several rows containing the following columns of info.

Name, several columns that represent how this person found us, such as sign, phone book, news paper, etc. etc. and then two more columns one for accepted one for denied. Each of these cells will have a specific value to represent that the cell is checked (to be specific Ö using the symbol font to make it appear as a check mark).

The amount of rows differs each day depending on how many people come in.

What I need to do using the data from each sheet to cover the whole week, is to count for each different possible how you found us column that is checked and count that against accepted and denied.

So in the end I could on a separate sheet have.

Method	Accepted	Denied
Phone Book
Sign	5	2
Update	3	1
Referral	7	3
Other	5	1

Something like that


Then I can see for each different method of finding us how many people who came in were accepted or denied, so we can see which method of advertising is working better etc. and make this data into a chart of some kind.

The chart would need to be across the bottom each of the methods, then for each method two bars displaying Accepted in green and Denied in red and up the left side would be numbers.

Can anyone point me to some posts or guides that would help me make this kind of data calculation and formatting it into a chart.

Thanks

[CAntosh]

Can you post a sample workbook (Using: Go Advanced > Manage Attachments) with fake or non-private data so we can see what you're working with?

[MilsonHQ]

Sure thing, here is a copy of the workbook I am using currently.

Be advised it does contain some macro code which allows me to double click in certain cells on each sheet and automatically add a checked or unchecked value.

But that is not essential to the functionality of what I am trying to do so does not need to be enabled to look at the workbook as I have entered some sample data already.

I have filled it with random data and duplicated most of the data into each different sheet so you can get an idea of what kind of info may be contained in the sheets.

What I need is for the first sheet the Weekly Stats sheet to be where a chart would be contained and maybe a simple table that contains all the above mentioned calculated values.

I have the totals already for each individual method of how they found us and also the overall totals of accepted and denied, just not the individual of how many accepted and denied per method of finding us, or the chart displaying that.

Thanks

[CAntosh]

The formula below is the one I got to work for cell C4. It will search the relevant column on each page for your check mark and run those counts against the approved/denied column. The downside of the formula is that my efforts to combine the dates into a unified range were hitting a wall, so it isn't the most versatile formula. Simpler sheet names or UDFs could be used to make this a little cleaner. The other issue you'll have is the data for your "other" column, which eschews check marks in favor of comments. If you want to count "other" entries like the others, it would make more sense to give "other" a column for check boxes, then a separate column for comments/specifics.

=COUNTIFS('Stat - 2016-03-28'!$D:$D,"=Ö",'Stat - 2016-03-28'!$K:$K,"=Ö")+COUNTIFS('Stat - 2016-03-29'!$D:$D,"=Ö",'Stat - 2016-03-29'!$K:$K,"=Ö")+COUNTIFS('Stat - 2016-03-30'!$D:$D,"=Ö",'Stat - 2016-03-30'!$K:$K,"=Ö")+COUNTIFS('Stat - 2016-03-31'!$D:$D,"=Ö",'Stat - 2016-03-31'!$K:$K,"=Ö")+COUNTIFS('Stat - 2016-04-01'!$D:$D,"=Ö",'Stat - 2016-04-01'!$K:$K,"=Ö")

[MilsonHQ]

Thanks thats great, I have simplified the sheet names to Monday, Tuesday, Wednesday etc. etc. as for the Other field, would there not be a simple modification I can make to your code to instead of looking for that symbol just check if that cell contains any text at all or if it is empty/blank?

I did something on each sheet to total that column using =COUNTA(J6:J24) for the top section (page 1) and =COUNTA(J32:J50) for the bottom section (page 2) of each sheet, could this kind of counting not be incorporated in some way?

*Edit* I think I have cracked it, might not be that elegant but.

I used the totals of all rows with accepted for each day, minus those that have blank values in the other column to let me know how many had text in it and were accepted, then did similar with the declined.

=SUM((Monday!K25)-COUNTIFS(Monday!$J6:$J24,"",Monday!$K6:$K24,"=Ö"))+SUM((Tuesday!K25)-COUNTIFS(Tuesday!$J6:$J24,"",Tuesday!$K6:$K24,"=Ö"))+SUM((Wednesday!K25)-COUNTIFS(Wednesday!$J6:$J24,"",Wednesday!$K6:$K24,"=Ö"))+SUM((Thursday!K25)-COUNTIFS(Thursday!$J6:$J24,"",Thursday!$K6:$K24,"=Ö"))+SUM((Friday!K25)-COUNTIFS(Friday!$J6:$J24,"",Friday!$K6:$K24,"=Ö"))

Seems to work.

Thanks again for everything it has been a huge help.

[CAntosh]

I'm glad you were able to find something workable! There shouldn't be any issues with making your range more specific. If you're confident in the accuracy of the smaller range, it's actually preferable. I used the entire column as a precaution against the possibility that you might have hundreds of entries on a given day in your actual data. If you're able to be more specific, doing so should help.

[MilsonHQ]

Thanks again for the help, it is working great so far