• Welcome to OGBoards 10.0, keep in mind that we will be making LOTS of changes to smooth out the experience here and make it as close as possible functionally to the old software, but feel free to drop suggestions or requests in the Tech Support subforum!

The Excel Help Thread

Can't you just do a COUNTIFS using the same conditions for your conditional formatting instead of trying to count the formatting itself?

Otherwise you need to use VBA i think
 
Problem is not every column has the same conditional formatting.

Maybe we can fix this. Currently there are about 30 rows of data being used. For rows 5, 6, 7, 9, 12, 14 and 16, I want the cells to be highlighted is x happens. However for rows 4, 8, 10, 11, 13 and 15, I want the cells to be highlighted if y happens. (rows are hypothetical and don't match up to post 30)

I haven't found a way to mass do this as one mass group and thus was forced to do them in two separate groups. Thus, mass conditional formatting for the entire group wouldn't work.
 
Last edited:
New excel problem:

I have an excel sheet where cells are highlighted based off a certain condition. Below that data, I would like to count how many cells in each row are highlighted.

I believe in the past you could do something like CountHL() or CountColor() but not sure about the newer versions. I'm expecting something like CountIF(range,criteria) but I'm not sure what I would but in the criteria part.

Pos rep for help?

Did a quick search and everything I'm seeing says you have to do some stuff in VBA. I seem to remember the ability to do this but am not sure. Others may no of a way without editing VBA.
 
Can't do just two CountIFs because the commas don't allow you to include more than one not sequential cell as it then messes up the criteria part.

Got something like this: =SUM(COUNTIF(C3:C6,B3)+COUNTIF(C8,B3)+COUNTIF(C11,B3)+COUNTIF(C13:C17,B3)+COUNTIF(C7,B7)+COUNTIF(C9:C10,B7)+COUNTIF(C12,B7)+COUNTIF(C19:C20,B7))

It works, and can then be used for each row, however I've now just got to do that 17 times.
 
Can't do just two CountIFs because the commas don't allow you to include more than one not sequential cell as it then messes up the criteria part.

Got something like this: =SUM(COUNTIF(C3:C6,B3)+COUNTIF(C8,B3)+COUNTIF(C11,B3)+COUNTIF(C13:C17,B3)+COUNTIF(C7,B7)+COUNTIF(C9:C10,B7)+COUNTIF(C12,B7)+COUNTIF(C19:C20,B7))

It works, and can then be used for each row, however I've now just got to do that 17 times.

Now one problem for this which might be pretty simple. I want to copy and past that above formula into each of the other cells in its column to calculate the totals of each row. However, when I past it, I want the row to continue to adjust (in this case it's all the Cx cells), but I want the Bx cells to remain the same.
 
Now one problem for this which might be pretty simple. I want to copy and past that above formula into each of the other cells in its column to calculate the totals of each row. However, when I past it, I want the row to continue to adjust (in this case it's all the Cx cells), but I want the Bx cells to remain the same.

Add $ where necessary in your formula? Is that what you're asking. Like $B$13? Or just put the $ before the row number?
 
Ahh, I figureed out ou my problem. I had been doing what Willis was saying and put the $ after the letter and number, but it needs to go before. Thanks.
 
Another dumb question

Trying to do a count in excel. I have data on one sheet that I wanted counted, but I wrote the formulas on another sheet.

How do I write the formula so that it counts all of the data I currently have in column A and any data I might add to the the rows below it in the future. So I want one sheet where I continue adding data and one sheet where the counts appear.

Currently I have: =COUNT(Input!A1:Input!A150)

I want something like: =COUNT(Input!A:Input!A), but that obviously doesn't work.
 
Another dumb question

Trying to do a count in excel. I have data on one sheet that I wanted counted, but I wrote the formulas on another sheet.

How do I write the formula so that it counts all of the data I currently have in column A and any data I might add to the the rows below it in the future. So I want one sheet where I continue adding data and one sheet where the counts appear.

Currently I have: =COUNT(Input!A1:Input!A150)

I want something like: =COUNT(Input!A:Input!A), but that obviously doesn't work.

=COUNT(Input!A:A)
 
ATP Excel Ninjas - Need help pulling sales people by location

Hey Pit Excel experts, I'm hoping someone can help me with an Excel template I'm making. My company has about 50 locations, and I want to list certain metrics by sales person for each location when that location is picked from a dropdown menu on the dashboard tab. The part I am unsure about is how to get the appropriate salespeople to be listed on the dashboard when a particular branch is chosen.

I have a list of data that includes branch ID and sales person name that can be used to match.

I can't just use a vlookup because it would only return the first salesperson that's listed for each branch. I need something that will return all unique values in the sales person column that are listed for each branch. Almost like I'd be able to do with a pivot table, but without using one. Do I need a macro for this or can it be done with a formula?

I want it to look like this:

Cell A1: Dropdown menu where I pick a branch

Cell A3: Formula to show the name of Sales Person 1 in the branch chosen in cell A1
Cell A4: Formula to show the name of Sales Person 2 in the branch chosen in cell A1

There can be up to about 10 sales people per branch.

Any help is greatly appreciated! Thanks.
 
Back
Top