• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNTIFS function for part of range cell

S

Saeed Ghazi Joolaee

New Member
Hi there,
I have a sheet with columns "date,data1,data2,data3"

I want to count data1 cells which are true if left 4 digit of date is 2022
How can I do that?
if I wanted whole date, it was simple with countif or countifs, but I don't know how can I do that with part of date?
 
Saeed Ghazi Joolaee
If Your date is real date then try to use Year(Your_date) = 2022 in Your formula.
If Your date is text date then try to use Left(Your_date,4) = 2022 in Your formula.
Many things would be more clear, if You could attach a sample Excel-file - which shows - what do You really have?
 
Assuming the 'date' column contains text rather than numeric date values
Code: 
= COUNTIFS(date, LEFT(date,4)&"*")
will perform a wildcard match for any text beginning with the current year whilst
Code: 
= COUNTIFS(date, 2022&"*")
had 2022 hard-wired into the formula.
 
Peter Bartholomew said:
Assuming the 'date' column contains text rather than numeric date values
Code: 
= COUNTIFS(date, LEFT(date,4)&"*")
will perform a wildcard match for any text beginning with the current year whilst
Code: 
= COUNTIFS(date, 2022&"*")
had 2022 hard-wired into the formula.
Click to expand...

Thank you very much, The second one [= COUNTIFS(date, 2022&"*")] worked for me.

Another thing...
Assuming date is string not numeric, How can I calculate >= and <= between two dates (for using just in COUNTIFS function again) ?
 
I found the answer myself ;)
we must use date range two times for CUNTIFS, one time for >= with start date another time for >= for end date
we cannot use logical statements like OR AND

any other comments...
 
Saeed Ghazi Joolaee
You can use two or more criteria with CountIFs.
But ... as You wrote ... those Your 'dates' are like text then there are some challenges ( to compare which string is bigger or smaller).
Use dates as real dates.
Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) The COUNTIFS function syntax has the following arguments:
  • criteria_range1 Required. The first range in which to evaluate the associated criteria.
  • criteria1 Required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".
  • criteria_range2, criteria2, ... Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.
 
@Saeed Ghazi Joolaee
I agree with @vletm in that it would be far better to work with numeric date values rather than dates as text. On the other hand, provided you are using an ISO formatted dates "yyyy-mm-dd" the alphabetic sequence of dates corresponds to the natural date order so you can include the two criteria to test for upper and lower bounds respectively.
 
You must log in or register to reply here.
Back
Top