# Microsoft Office Application Help - Excel Help forum > Excel Charting & Pivots >  > [SOLVED] Chart for counting how much time spend on customer

## IIIdefconIII

As example:

2018-07-13_13-25-33.png

I like to have 2 charts. If possible in one.
That in colum B (klant) are customer shortnames and in column I there is the time i have spend on that costumer.
I like to have a circle chart that shows me how much time i have spend on which costumers.

Something like this:
https://www.google.nl/url?sa=i&sourc...31568579226074

Could you please tell me how to do this and if its even possible with the time values in column I?

----------


## AliGW

I have no idea what you are asking, sorry!

Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

Use concise, accurate thread titles.
Your post title should describe what you want to do, not your anticipated solution.Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.Responding to a request to change your thread title by doing so is mandatory.
*To change a title go to your first post, click EDIT then Go Advanced and change your title.
*
*No help to be offered, please, until the OP complies with this request.*

----------


## Glenn Kennedy

Also,  Attach a sample sheet.  Your picture is very small and non-editable.

Unfortunately the attachment icon doesn't work at the moment.  So, to attach an Excel file you have to do the following:  Just before posting, scroll down to *Go Advanced*  and then scroll down to *Manage Attachments*.  Now follow the instructions at the top of that screen.

----------


## IIIdefconIII

Changed  :Smilie:

----------


## AliGW

Thank you.  :Smilie:

----------


## IIIdefconIII

OK so i got the chart working, BUT,  the system think that STX are seperate customers, they should be together by name

----------


## AliGW

You need to use the Klant column as your axis labels and the Totaal column for the actual data.

----------


## AliGW

> STX are seperate customers, they should be together by name



Then you need to create a summary table and use that as the source for your chart.

----------


## IIIdefconIII

> Then you need to create a summary table and use that as the source for your chart.



To be honest, i have no idea what youre meaning. I will go play with it more instead of wasting youre time. Thank You much for this!  :Smilie:

----------


## AliGW

Who said you were wasting our time? If you don't understand, that's fine - I'll try to explain more clearly. That's what this place is about - I come here to help people!!!  :Wink:

----------


## AliGW

Summary table:

*Excel 2016 (Windows) 32 bit*

B
C

*18*
STX
1:55

*19*
GMS
0:10

*20*
ML
2:45


Sheet: *Blad1*



*Excel 2016 (Windows) 32 bit*

C

*18*
=SUMIF($B$10:$B$16,B18,$I$10:$I$16)


Sheet: *Blad1*



Once you've created this, change the data references in your chart to point to it.

Have a go - let us know how you get on.

Cheat sheet attached.  :Wink:

----------


## IIIdefconIII

Thank you for the kind words, i tried to pase the code and it gives me a error, i tried to change the sumif to SOM.ALS cause im using a dutch version, but that one is also not working. Shouldnt the code be in another cell D instead of C? cause in C the time data is there.

    Sry just found the example

PS    Ah i get it now

----------


## AliGW

Open the attachment - everything will be converted automatically.

----------


## IIIdefconIII

Thanks to you!

----------


## AliGW

If that takes care of your original question, please select _Thread Tools_ from the menu link above and mark this thread as SOLVED. Thanks.

----------


## IIIdefconIII

It does, the next thing is, every week i have a new sheet with different customers, is there any way to more automate this?

----------


## AliGW

But it doesn't, because you are now introducing more requirements!

OK - can you please provide a sample workbook that realistically reflects your real data? Covering all requirements, please, not just parts of what you want.

----------


## IIIdefconIII

Sure can i send this in private? Cant have this workbook on the internet  :Frown:

----------


## AliGW

No, you can't, but you can copy the workbook, and then desensitise the copy that you post here. That means provide dummy data in the same layout as your real workbook.

----------


## IIIdefconIII

Please look at N65, thank you very much. If its to much to ask let me know, i may need to follow some courses then.

----------


## AliGW

OK - so I see what you have asked for. Please would you copy and paste the description of what you want here into your next post so that all members can see what you are looking for. I am not able to help you with a chart with two rings - sorry. My charting skills are not that good. I hope that Andy Pope will see this - he's a bit of a charting wizard!  :Smilie:

----------


## IIIdefconIII

Alrighty, thanks

I like to have this chart
instead of % id like the time spend on that costumer

then a second ring around it with the total time of all cosutmers together withtout STX (thats us), so we can
see how many time we have billable, based on a 40 hours week., not sure if this is possible

last but least, it would b greate if we can have a third ring around it that will look into all values on all sheet and count them together

----------


## AliGW

For the benefit of others, "this chart" is a donut chart with the percentages shown.

----------


## IIIdefconIII

I am kinda proud of myself now, thank you again, lets wait for Andy now

----------


## AliGW

Why? The workbook looks the same as the last one to me. Have you solved the issue? If so, can you show us how?

----------


## IIIdefconIII

m could that be cause i already uploaded a sample.xlsx, idk anyway, im trying to recreate youre formula into my own excelsheet but things wont add up, the timings is incorrect. Im trying to resolve this but i cant find where it goes wrong. Maybe you want to look into it one more time? The values are on B200. Like i said earlier, every week there will be a new sheet, so i need to put those values down below so i have anough space to add more lines if there is more work.

The timing in C207 is way to high and incorrect.

----------


## AliGW

OK - I will have a look shortly.  :Smilie:

----------


## AliGW

You need to reference the correct columns:

=SUMIF($B$6:$B$196,B201,$I$6:$I$196)

----------


## IIIdefconIII

I think i need Andy, This time i kinda got it working, but now its also showing the 0:00 zero timers cause of the table, can there be a statement if value is 0:00 or < 0:01 then dont show

----------


## AliGW

Try this:

=IF(SUMIF($B$6:$B$196,B201,$I$6:$I$196)=0,"",SUMIF($B$6:$B$196,B201,$I$6:$I$196))

----------


## IIIdefconIII

tried this:

=ALS(SOM.ALS($B$6:$B$196,B201,$I$6:$I$196)=0,"",SOM.ALS($B$6:$B$196,B201,$I$6:$I$196))
fails and $B$196,B201,$I$6 gets highlighted.

Do i need to alter some points commas or so for translation? cant find anything about that on the excel function name translation

----------


## AliGW

You need semi-colons, not commas, for your locale:

=ALS(SOM.ALS($B$6:$B$196;B201;$I$6:$I$196)=0;"";SOM.ALS($B$6:$B$196;B201;$I$6:$I$196))

----------


## IIIdefconIII

cells become white, also the cells with 0:10 ten minutes as example. the name still stays in the chart

Im putting this on the Total columns, right?

----------


## AliGW

The only way you can stop them showing on the chart is by deselecting them.

Give me a few minutes - I have an idea. I will post when I have checked it.

----------


## AliGW

Try basing the chart on cells J201 onwards. This is a summary of the summary table. Hopefully it will work for you.

I suspect there's an easier way of doing this with pivot charts, but I would be using guesswork!

I still hope that Andy Pope sees this ...  :Wink:

----------


## IIIdefconIII

You are great! Thank you so very much, if you ever need IT related help, dont hesitate to ask! 

But its still showing all names only this time they overlap each other

Thats cool, some dark magic on it and it works, anyway all the 0:00 are still visable in the chart, they are overlapping the other real data,
Im also trying to move, when i create a new J101, to D101, same as K101 to E101. This to make it a little bit more compact. I believe when i need more rows, i can just insert them and the rows is formula will correct the formulas.

Im so graitfull for this!


EDIT: I Reacreated the chart and it seems to work now.

----------


## DairyQueen

I have a similar problem, but I don't need a chart.  The Sumif function seems to work until you get too many iterations.  It was working without the F4 adding the $ signs, but quit and after I added those because I saw it in this thread, it still doesn't work for the B column category with a lot of iterations, but still works for the category with fewer iterations.   :EEK!:

----------


## IIIdefconIII

After doing alot of editing and playing around i got this to work:

----------


## AliGW

Glad to have helped, even partly.  :Smilie: 

Thanks for the offer of IT help - not sure I need any at the moment, though, but thanks.  :Wink: 

If that takes care of your original question, please select _Thread Tools_ from the menu link above and mark this thread as SOLVED. Thanks.

----------

