Solved

# Using Countif for Variable Search Criterea in Excel

Posted on 2011-03-24
232 Views
For the attached Excel sheet I would like the countif formula in P1 to be copied down column P, however I would like the formula to use whatever value is in Column O (same line) as a variable and return the value in Column Q (same line). The search area will always remain the same - A2:G5014.
0
Question by:bpfsr
• 2

LVL 43

Expert Comment

Whilst you are sending the file try changing A2:G5014 to \$A\$2:\$G\$5014
0

Author Comment

yup, forgot the file... SP2.xlsm
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 500 total points
enter this formula in P2 and copy down

=COUNTIF(\$A\$2:\$G\$5014,"*"&O2&"*")
0

## Featured Post

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…