Forum Home
Press F1
Thread ID: 79926	2007-06-06 00:18:00	Excel XP: Countif function query	tinakarori (5695)	Press F1

Post ID	Timestamp	Content	User
556497	2007-06-06 00:18:00	The syntax of the Countif function is =Countif(range,criteria). So far as I have been able to find out, it is not possible to insert another formula or function (apart from a cell reference) in the Criteria argument, ie it doesn't seem possible to "nest" functions. I wish to count three-part information codes in a range, eg "1-12-3", which are constructed by a concatenation formula from three other ranges: The first field is the Area code, eg 1 The second field is the year-end, eg 12 (December) The third field is the month received, eg 3 (March) The dashes are simply field separators, that are iserted by the formula. For reporting purposes, I want to count all the Year-end (eg Dec-31) returns received to date for each Area, regardless of which month they were actually received in (although I still wish to retain the Month Received in the concatenation, for other analysis reasons). Thus what I need to do is place a part-variable in the Criteria argument, eg "1-12-n", where "n" can be any month number. Can anyone suggest a way to achieve this, without using a Macro or VBA?	tinakarori (5695)
556498	2007-06-06 02:08:00	For something like this I would use the sumproduct function. As an example =sumproduct((criteria 1)*(criteria2)) where criteria 1 is (A1:A5=AreaCode) and criteria 2 is (B1:B2=Yearend). If the range with the criteria is concatenated together then you will need to strip out the relevent part of the field using left and mid functions, although if you still have your three separate ranges available prior to concatenation you maybe able to use these. This may seem a little cryptic but essentially all this is doing is evaluating whether the first criteria is met and if so set it to TRUE and then repeat with the second criteria. The multiplication simply multiplies the TRUE/FALSE criteria together and sums up (NB True x True = 1 and anything else = 0). Cheers Dave	odyssey (4613)
556499	2007-06-06 02:20:00	have you tried =COUNTIF(range,"1-12*") cheers	Sam's Dad (11848)
556500	2007-06-10 11:16:00	Hi How does your solution work Sam's Dad? Is that syntax correct? Also Dave where you provide sumproduct examples, you are best to use matching ranges - the sumproduct formula cannot handle varying ranges (in terms of the number of columns/rows in the criteria part). Tinakarori - Dave had the right idea with the sumproduct formula - where you want to use multiple criteria in either a sumif or countif function, then the sumproduct formula is one way of doing that. Have a read of the contributions from Aladi Akyurek in this thread (www.mrexcel.com) on MrExcel.com - it explains very nicely the various techniques for using a sumproduct formula in this instance. Andrew	andrew93 (249)
1