HI. I am, a new user trying to figure out how to count text items in a database of thin client peripheral device details using Excel 2016. The database has about 9,000 records, I am attaching the first 50-or so as a sample. The database contains 4 columns. The first column is a Site number, the second column is the number of a thin client device at the site, (there can be up to 9 thin client devices at a site number and then the next 2 columns are the 2 VGA connection ports with a text description of the function that each of the connected VGA devices is tied to. Some cells are blank. I need to count some of the devices based on a set of 'rules' that I am outlining below.

I am able to easily count the number of connected VGA devices using this formula in column 'E' =COUNTIFS(C3:D3,"*",C3:D3,"<>GRILL"); I am looking for help in coming up with a formula to count the other items using these rules.

* I assume this count formula would be similar to the one for the rule above?*

Please let me know if you can help me avoid having to manually go through each line item to get the counts or if you have any questions or need additional information. Many Thanks!

I am able to easily count the number of connected VGA devices using this formula in column 'E' =COUNTIFS(C3:D3,"*",C3:D3,"<>GRILL"); I am looking for help in coming up with a formula to count the other items using these rules.

COUNTING RULES: |

VGA MONITOR COUNT = Count 1 VGA monitor for each port that is not blank AND does not contain "GRILL" |

I am using this formula for this first count: COUNTIFS(C3;D3,"*",C3:D3,"<>GRILL"). This seems to work. |

KEYPAD COUNT = Count 1 keypad for each VGA Monitor but only for the first instance of "DT" within a Site #, also excluding all "FRY" stations |

I need to determine a formula to count these keypads but excluding the duplicate 'DT' counts and all 'FRY' station instances. |

GRILL TOUCH MONITOR COUNT = Count the first instance of "GRILL" within a Site # but exclude all other instances at that same Site # |

Hi All,

First time poster, long time reader. I have been having an issue with a formula I am inputting into the data validation widget to create a dynamic dependant dropdown error. The formula is as follows:

=OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense Category]],Setup[[#Headers],[Savings]:[Miscellaneous]],0)-1,COUNTA(OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense Category]],Setup[[#Headers],[Savings]:[Miscellaneous]],0)-1,20,1)),1)

When the formula is put into the "Data Validation" widget, it returns the "There's a problem with this formula" error.

Normally this would make sense and indicate that there was an error with the formula, but when I put the formula directly into the cell to test it, I can see the results. Although, admittedly, the results return a "#VALUE!". However, when i select the formula in the cell and press F9, it returns the desired result.

I have attached both the workbook (the tab in question is "Expense Input" but it references the tab named "Setup".

Any ideas or help would be greatly appreciated.

First time poster, long time reader. I have been having an issue with a formula I am inputting into the data validation widget to create a dynamic dependant dropdown error. The formula is as follows:

=OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense Category]],Setup[[#Headers],[Savings]:[Miscellaneous]],0)-1,COUNTA(OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense Category]],Setup[[#Headers],[Savings]:[Miscellaneous]],0)-1,20,1)),1)

When the formula is put into the "Data Validation" widget, it returns the "There's a problem with this formula" error.

Normally this would make sense and indicate that there was an error with the formula, but when I put the formula directly into the cell to test it, I can see the results. Although, admittedly, the results return a "#VALUE!". However, when i select the formula in the cell and press F9, it returns the desired result.

I have attached both the workbook (the tab in question is "Expense Input" but it references the tab named "Setup".

Any ideas or help would be greatly appreciated.

I'm trying to build a replace formula, but want the data to be pulled from a list that has two columns with 107 rows. The first column would have staff names, and the second would have staff community of residence. Essentially if a staff name is entered in a cell, I want the text to be replaced with the community associated with the staff.

]]>I have a multiple spread sheets that have columns named select your name with a number infront of it.

There is only 1 name per in these columns per row. I need to get all those names into the first column. I'm looking for a way to do this without using =A2&b3&c2 (etc...) reason I don't want to use that formula is because each sheet has this information in different columns and I don't want to have to retype this formula all the time.

If there's a simple way to do this please let me know. attached is an example.

There is only 1 name per in these columns per row. I need to get all those names into the first column. I'm looking for a way to do this without using =A2&b3&c2 (etc...) reason I don't want to use that formula is because each sheet has this information in different columns and I don't want to have to retype this formula all the time.

If there's a simple way to do this please let me know. attached is an example.

I am trying to drag a formula through a column without the cell range overlapping. Actually the cell range is a merged cell on another sheet. Here is the formula: =IF(COUNTBLANK('Sheet1'!F4:F6)=3, "","Done"). The formula itself works as intended but I want to drag the formula through the column, but the next row will have a range of F5:7 rather than F7:F9.

Since I merged 3 rows I the range needs to be F4:F6, then F7:F9, F10:F12, etc., but if I drag the function it goes F5:F7, F6:F8, F7:F9

]]>Since I merged 3 rows I the range needs to be F4:F6, then F7:F9, F10:F12, etc., but if I drag the function it goes F5:F7, F6:F8, F7:F9

My problem is as follows: I have the below schedule for a series of games between 4 teams. For these games, TeamX is allowed to scout any games of their next opponent prior to the match-up with TeamX's match-up with said opponent. So for example, AvsB is Game 1, CvsD is Game 2, AvsC is Game 3. A would be able to scout Game2 because Team C is their next opponent at the time of Game 2. How I determine who a next teams opponent is formulaically, then determine which games of that opponent they can scout?

I.e., In column for, I would like to formulaically "A" in the empty column for Game # 4 & 6, because Team D is Team A's next opponent at the time Team D plays those games. The fourth column is a helper column on the assumption it is easier to match to a martial match-up given there are are two columns a team could be found in.

]]>I.e., In column for, I would like to formulaically "A" in the empty column for Game # 4 & 6, because Team D is Team A's next opponent at the time Team D plays those games. The fourth column is a helper column on the assumption it is easier to match to a martial match-up given there are are two columns a team could be found in.

Game # | Away | Home | ||

1 | A | B | A-B | |

2 | C | D | C-D | |

3 | A | C | A-C | |

4 | B | D | B-D | |

5 | B | C | B-C | |

6 | C | D | C-D | |

7 | A | B | A-B | |

8 | A | D | A-D | |

9 | B | C | B-C | |

10 | A | D | A-D | |