Countifs

chris007

Board Regular
Joined
Jun 27, 2011
Messages
82
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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)
 
Upvote 0
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
MNUVWX
1DateDataCount
28/25/2017DER3
38/26/2017VIC3
48/27/2017VIC
58/28/2017DER
69/1/2017VIC
79/2/2017DER
89/3/2017VIC
99/4/2017VIC
1010/4/2017VIC
Sheet
 
Upvote 0

Forum statistics

Threads
1,223,919
Messages
6,175,371
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top