MrExcel Publishing
Your One Stop for Excel Tips & Solutions

countif formula with multilple conditions and wildcards


Posted by jason on October 14, 1999 8:20 AM

i update a large cd list that is posted online. i use an excel spreadsheet to format the list multilple ways, plus keep basic statistics on the collection. i keep a count of the number of 2 cd sets, if it is recorded live, etc. sometimes a cd fits in more than one category at a time, and i type both into the box. to keep an accurate count of total number of cds, including the extra cds that come with cd sets, i have an extra column that i use to handle overflow. if it is a 2 cd set, i type a 1 in the overflow column. if it is a single cd, the cell is left blank. this way, i count the number of rows, plus all of the numbers in the column to get an accurate count of the entire collection.

for example, if i have a 2 cd set that is recorded live and is an import, in column A i would have a 1 (to account for the extra cd associated with the set) and in column B i would have 2 cd live import. i want to make sure that i can count this entry as 2 live cds and 2 import cds.

how can i manipulate the countif statement to account for all of this?

thanks,

Jason


Posted by Chris on October 14, 1999 8:45 AM

Jason,

The best way to handle multiple conditions to a countif is with the use of array formulas. I'm guessing you are not familiar with these, but there is an overview of the concept on MrExcel's page. The formulas can tend to get a bit complex, so you may want to provide a more detailed description of exactly what you need and how things are arranged, so that someone could give a more specific answer. I'm also suspecting that the spreadsheet is not set up in the best fashion either. There may be some places where a change to a column here or there could make your life easier.

Chris

Posted by Stuart on March 08, 2000 2:05 PM


Hi Jason

You could try either a Dcount or Dcounta

Stuart