# Posts by 63falcondude

• ## Add Rounding to an Existing Formula

Try this:

=MROUND(K16*1.03,0.25)

• ## Trying to use data bar formatting

Welcome back. The formula repeats these --> █ n times where n is the number of cells in the row that are not empty. The repetition of these black rectangles are what creates the bar.

Just highlight the formula in post #9 and paste it into cell A2.

If you want to combine data in D4 and D6 into cell B6, you can use CONCATENATE like this: =CONCATENATE(D4,D6) or even easier, =D4&D6

Note that if you want to put a space in between the last name and address, you can do that like this:

=CONCATENATE(D4," ",D6)

or my preferred method, like this:

=D4&" "&D6

Put either of those formulas in cell B6.

• ## Sumproduct by Date Value

I'm glad that you found a working solution.

In the future, uploading a small representative sample of your data along with the desired outcome of the formula will be the best way to help us help you.

• ## Sumproduct by Date Value

It's difficult to provide a working formula without seeing a small sample of your data.

That being said, try this:

=SUMPRODUCT((MONTH(Bank[Date])=MONTH(A2))*(YEAR(Bank[Date])=YEAR(A2))*(Bank[Category]=B1)*Bank[Value])

• ## Trying to use data bar formatting

Upon further inspection, this (in column A) works better:

=REPT("█",COUNTA(B2:O2))

You can keep the Conditional Formatting formulas the same or use these:
1) =COUNTA(B2:O2)=14 (Green font)
2) =COUNTA(B2:O2)<14 (Yellow font)

• ## Trying to use data bar formatting

Here's another option if you want to change the color of the bars:
https://stackoverflow.com/ques…-data-bars-based-on-color

See attachment to see this used in your application.

A2 =REPT("█",10*COUNTA(B2:O2)/14)

Used 2 conditional formatting rules:
1) =COUNTA(B2:O2)/14=1 (Green font)
2) =COUNTA(B2:O2)/14<1 (Yellow font)

• ## Trying to use data bar formatting

I don't think that there is much versatility with the data bars (changing color conditionally or disappearing completely).

One option, if you want to have a green check mark show up in the cell when the value reaches 100%, you can add another Conditional Formatting rule using icon sets.

Highlight A2:A333 > Conditional Formatting > Icon Sets > 3 Symbols

Now, with any of the cells in A2:A333 selected, Conditional Formatting > Manage Rules > Edit Rule > Check the "Show Icon Only" box > change the bottom two icons to "No Cell Icon" > change the first Value to 1 and the Type to Number > OK > OK

This will show the bars and a green check mark when the bar hits 100%.

• ## Trying to use data bar formatting

You're welcome. Happy to help.

You only have to put the formula in the first cell (A2) and then drag it down as far as needed. The rows will automatically adjust in the formula as you drag it down the column.

• ## Trying to use data bar formatting

Also, I would change Minimum and Maximum from Automatic to Number (0 and 1 respectively).

• ## Trying to use data bar formatting

I created a small sample and attached it here.

As they said, in A2, use this formula: =COUNTA(B2:O2)/14

Copy that formula down column A.

Select A2:A333 > Conditional Formatting > Data Bars > Pick which one you want

Now, with any of the cells in A2:A333 selected, Conditional Formatting > Manage Rules > Edit Rule > Check the "Show Bar Only" box > OK > OK

• ## Not Seeing As Many Posts ???

I just joined looking to help but as mentioned, I noticed that there doesn't seem to be much activity here.

This is not the norm for this forum?

• ## Vlookup to find date from 2 separate sheets

Another option, assuming the same locations shown from post #2, and that there are no duplicates, is this:

C3 =SUMIFS(N:N,L:L,A3,M:M,B3)

Make sure to format column C as dates.

• ## Vlookup to find date from 2 separate sheets

You have a couple of options. Here's one:

Assuming that LIST1 is in A2:B5, and LIST2 is in L2:N5 (headers in row 2), try this in C3

=INDEX(N\$3:N\$5,MATCH(A3&B3,L\$3:L\$5&M\$3:M\$5,0)) Ctrl Shift Enter

• ## Count Unique Values by category

One option is to use a helper column (column C here) to show duplicate products.

C2 =COUNTIF(A\$2:A2,A2)

Then try this in E2:

=COUNTIFS(B:B,D2,C:C,1)