Adding a generic AND/WHERE clause to a group of formulae

borntorun75

Board Regular
Joined
Jul 12, 2010
Messages
57
I have a load of formulae in a spreadsheet. COUNTIFs, SUMIFs and some just general COUNT functions. A mixed bag. All these formulae are working on data in a named range called Table_Query_from_Data_Source. It's actually data pulled through from an MSSQL data table.

I'm tacking an extra column into the range, which is going to be a date. I'll call the column Date_Period, for ease of explanation.

Here's an example of one of my existing formula

=COUNTIF(INDEX(Table_Query_from_Data_Source,,MATCH("Product_Type",Data_Columns,0)),"=*Cars*")

It's taking the data range, searching for the column called Product_Type, and counting the number of entries that have the word 'Cars' in any of the rows. Straight forward enough.

I want to change each of the formulae I have to add in the clause 'AND/WHERE Date_Period = a date I specify'

With a bit of work, I could rewrite each formula to take account of it. It got me wondering though whether there might be a simple clause I could add into each calculation that essentially left the formula as is, but tacked on some logic which only did it where the Date_Period was also/only the one I wanted. So, it's taking account of a column, new to the formula. Something like adding an AND clause into the start/end of it.

Any thoughts, or tips to make my life simpler, or is it one where I'd have to dig into each formula ?

Thanks in advance. Using Excel 2007.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,807
Messages
5,513,516
Members
408,956
Latest member
DianChris

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top