Excel Formula Help

  Beffuddled 12:09 20 Feb 09
Locked

Hi,

I wonder if anyone can assist, I have a database of courses and I need to calculate in my stats the number of completed courses in one column, if criteria in another column meets certain specifics. The problem is the completed column contains dates or text, the other column contains just text...

I'm not explaining this very well. Consider I have three columns one headed course type and in there it lists the name of a course, ie Maths, English, Computers etc, The second column is labled completed, in this column the input is either the date of completion or the word Incomplete, the third is the stats column, in a cell in the stats column I need to enter a formula that ignores the 'Incomplete' text reference in the Complete column and provides the sum of all the cells containing completion dates but only if the information contained in the 'Courses' column states 'English' for example.

Conversley I will then need to do the exact same thing but with the formula recognising the word 'Incomplete' and ignoring the dated entries in the column 'Completed'. I'm okay using single formula such as IF/COUNT/COUNTIF etc but I think this may require an amalgamation and I'm completley stuck!

I would really appreciate any help you can give me.

  VoG II 12:26 20 Feb 09

Assuming that the dates are Excel dates and not text dates then for the dated values

=SUMPRODUCT(--(A1:A100="English"),--ISNUMBER(B1:B100))

and for the Incompletes

=SUMPRODUCT(--(A1:A100="English"),--(B1:B100="Incomplete"))

Notes:

-- is two consecutive minus signs.

Ranges must be the same length and can't be whole column references (like A:A) except in Excel 2007.

  Picklefactory 12:28 20 Feb 09

Do you mean for the 3rd column that you want a single cell to show a total number of completed courses for each subject? ie a cell showing how many English courses complete, another cell for Computers etc?
Sorry, not quite sure what you're after.

  Picklefactory 12:29 20 Feb 09

There you go, beat me to it by a mile. :-)

  Beffuddled 14:51 20 Feb 09

VoG you are WONDERFUL! I could kiss you. Thanks!

Thanks for the offer of help Picklefactory I really appreciate it.

This thread is now locked and can not be replied to.

The Legend of Zelda Breath of the Wild review: Five hours with Zelda on the Nintendo Switch

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…