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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,112
Messages
5,857,450
Members
431,880
Latest member
kar2rost

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top