Countifs

[chris007]

Hello all,
Thank you for the forum this is great and have utilised it many times on my journey to better work with excel. I am using excel version 2013

My Dilema is:

I have developed the formula "=countifs(V2:V5447,"VIC",M2:M5447,"*Sept*")

Cell format for Column "M" is date format
Cell format for Column "V" is general

The resultant output of the formula is"0"

I have changed the Format of Column "M" to general and entered the dates as a TXT rather than Date and the resultant outcome is still "0"

Can someone shed any light for me

thanks
Chris

 

[mc5476]

Re: counifs giving headache

From what I can guess, it is because the values in column M are dates. Dates correspond to an actual number, and therefore wouldnt actually contain "sept". What you could do, if you can add the =month() formula on the date field. This will capture just the month and will return just a number. So you could adjust your current formula to say: =countifs(V2:V5447,"VIC",M2:M5447,9)

 

[AhoyNC]

Re: counifs giving headache

I would change your column M back to dates and then you could use either SUMPRODUCT or COUNTIFS as below.

Excel Workbook
	M	N	U	V	W	X
1	Date			Data		Count
2	8/25/2017			DER		3
3	8/26/2017			VIC		3
4	8/27/2017			VIC
5	8/28/2017			DER
6	9/1/2017			VIC
7	9/2/2017			DER
8	9/3/2017			VIC
9	9/4/2017			VIC
10	10/4/2017			VIC
Sheet