# Help with Charting different dates for Vendors

1. ## Help with Charting different dates for Vendors

Hi, I have a slight problem.

I'm trying to make a bar chart with our vendors to see who worked together during the same period. My problem is that some vendors were working intermittently during the year.

Is here a way for me to make a bar chart for this?

I attached a snip and a sample workbook.

Thank you!

Capture.PNG

2. ## Re: Help with Charting different dates for Vendors

Your workbook has the same content as the screenshot, i.e. a few colored cells. I assume that this is the desired output. You don't say.

In order to generate that output, please provide the source data and explain the logic to arrive at the result.

3. ## Re: Help with Charting different dates for Vendors

Hi, Teylyn

Sorry about that. Here's the file,

So basically what's happening is we have several vendors/subcontractors who invoiced us during different periods and I want to make a horizontal bar chart (similar to a gantt chart), so we can see which vendors worked together during each quarter.

Thank you!

4. ## Re: Help with Charting different dates for Vendors

get rid of the merged cells in your data table and add the correct vendor name to each row.

In the result grid, make sure that the cells with the month name contain real dates, the first of each month. You can format the cells to show just the month name.

Now you can fill the table with formulas that count the number of invoices for that vendor in that month. For example

=COUNTIFS(\$C:\$C,">="&G\$4,\$C:\$C,"<"&EDATE(G\$4,1),\$B:\$B,\$F5)

This formula counts the cells where column C is greater than the date in G4, column C is smaller than the date in G4 plus one month and column B is equal to the value of F5. See the attached file.

As a final step you can use conditional formatting to format zero results with white text on white and number results with blue text on blue.
Use a conditional formatting rule with a formula. The formula for white cells is

=G5=0

The formula for blue cells is

=G5>0

The format is applied to the body of the table. G5 was selected when the format was created. Note that there are no \$ signs in the formula.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1