# Excel Formula Help

Beffuddled 12:09 20 Feb 2009
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!

VoG II 12:26 20 Feb 2009

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 2009

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 2009

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

Beffuddled 14:51 20 Feb 2009

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.

Elsewhere on IDG sites

OnePlus 5 review

50 best online Adobe XD tutorials