COUNTIFS Using Date As Criteria With Wild Card
Mar 31, 2014
I'm trying to use the COUNTIFS function & have run into trouble because the 2nd criteria is a date. Typically I would just set the range & then put "01/01/2014" if I was looking for all of the January 1, 2014 cells. However in this spreadsheet my source data contains the date with the time. (01/01/2014 11:27:39 AM). Becasue the time is included in the cell, the function returns no results when I set teh date.
I tried to use a wild card "01/01/2014*" which I thought would bypass the time but it still retuns no results. Is there a way to use a windcard with a date cell?
View 3 Replies
ADVERTISEMENT
Nov 30, 2007
If any excel expert who know how to compare names that are similar to one other with a wild card,
for example
A
COLUMBUS STEEL CASTINGS CO. 2ND LIEN (AKA BP METALS)
B
BP METALS AKA COLUMBUS STEEL Total
A
LS POWER - BROADWAY GEN FUNDING LLC
B
BROADWAY GEN FUNDING, LLC Total
View 9 Replies
View Related
Nov 10, 2009
My live sheet is a lot more complex but this will help me well on my way.
Ill let the example say everything:
Sheet3
ABC1Condition 1Condition 2Condition 32red lorrybig £ 92,621.98 3yellow carbig£ 145,498.33 4yellow vanbig£ - 5pink carsmall£ - 6red carsmall£2,552,404.50 7pink lorrybig£ 862.37 8green carbig£ - 9green lorrybig£ 707,083.31 10red vansmall£ 30,010.73 11green carbig£ 2,276.13 12 13 14Total of all cars that are big: 15expected answer £ 147,774.46
Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Apr 25, 2014
Data in B2 looks like this: "[13.0]DEV;[13.0]AUDIT" or can be "[13.0]DEV"
I need to wild card B2.
Although, not working.....my formula looks like this:
=if(and(A2="audit",B2"*"&"[13.0]AUDIT"&"*"),"Closed Audit",A2)
View 9 Replies
View Related
Feb 22, 2010
Im trying to use a wild card to count the number of cars regarless of color (Red, Blue and Black) in Maryland which are USED.
A: Cars Red, Cars Blue, Cars Black.
B: State
C: Type: NEW, USED
So I tried
=SUMPRODUCT((A2:A3100="Car~*"),--(B2:B3100="Maryland"),--(C2:C3100="USED"))) and tried with countif
=Countif(A2:A3100="Car~*"),--(if(B2:B3100="Maryland"),--(if(C2:C3100="USED")))
View 9 Replies
View Related
Dec 6, 2011
Suppose the data is:
beaut* 1
abc* 2
and I use the command:
Vlookup("beautiful", A1:B2, 2, false)
I want this formula to return 1. I have done it before, the other way around when the wildcard is used in the formula but not in data.
View 6 Replies
View Related
Apr 3, 2013
Heres My Code...
Code:
Sub HideDatedSheets()
Dim wb As Workbook
Dim ws As Worksheet
Dim dDate As Date
[Code]...
So what im trying to do is keep the Union* sheets shown as well as the Report* sheets shown. But i believe i have the code wrong because they are not showing up when the work book opens...
Trim(ws.Name) = "Union*" Or _
Trim(ws.Name) = "Report*" Or _
View 4 Replies
View Related
Feb 3, 2014
I have a workbook containing 2 sheets. In the first sheet I have a list of values, let's say that the list contains names:
Worksheet1
Column A
Peter
Paul
In another worksheet I have another column, also containing names:
Worksheet2
Column A
James Harrow
Paul Givens
I wish to perform a vlookup in worksheet2 but cannot use wildcards in the table array :
Vlookup ("*"&ColumnA&"*";Table1[Table or table and column];1;false). What I want should look like the following: ("*"&ColumnA&"*";"*"&Table1[Table or table and column]&"*";1;false)
In this problem, it is not appropriate to query back to front; i.e. vlookup worksheet1 instead of vlookup in worksheet 2.
I have seen an interesting post that uses macros (on this site, I think) but was not able to implement it.
View 3 Replies
View Related
Jun 26, 2013
The usage of the wild card character is not getting me the results I need for a formula I am using at work. When the wild card asterisk character is removed, I get the correct result but only for an exact match of the particular text. I need it to match the "Particular Text plus another text. The Text is CBKC. Then there is also CBKC 5400 and so on. I need to have the wild card expression character before and after the CBKC characters. The formula is below that I used.
=sum(--k2:k500),--(c2:c500=" Y"),--(D2:D500=" Permit"),--(s2:s500="*CBKC*))
Excel doesn't like the way I am expressing the wild card inside the double quotes......the column getting added if the matches are found is column K. The other columns have to meet the criteria in the formula.
View 6 Replies
View Related
Mar 15, 2013
The attached file Countifs issue.xlsx shows my attempt to construct a "CountIfs" formula that finds the one record out of ten that matches the following criteria:
a) Col A has 10 IDs. 3 of them are between 40 - 49, and another 2 are alphanumeric. This formula SHOULD identify these 5:
=COUNTIFS(A2:A11,">39",A2:A11,"<50",A2:A11,"4AB",A2:A11,"40C")
Doesn't work, It finds the 3 numerics but returns "0" when the two alpha-numerics are added.
b) Col B has a list of dates. 5 of the 10 are this month. This formula SHOULD identify them:
=COUNTIFS(B2:B11,(EOMONTH(NOW(),-1)+1),B2:B11,(EOMONTH(TODAY(),0)))
Doesn't work. It returns a value of "0".
c) Col C shows the Status for each record. 5 are "A" and 5 are "B". The following formula DOES find all the "B"s
=COUNTIF(C2:C11,"B")
But obviously I need the full formula to find the one record out of the ten that meets all the criteria.
in (a) and (b) and enable me to combine all three sections.
View 4 Replies
View Related
Oct 18, 2012
I am looking for a formula which will allow me to count unique values in a database, based on multiple criteria. Sample file attached (Formula required in cells J and K).
View 6 Replies
View Related
Nov 20, 2013
I need to extract the credit card expiration date from a cell. For example
1111 1111 1111 1111 1/17 code 1111
I was able to get the /17 using =MID(C77,FIND("/",C77),4)
However, I can't get the "1" before the /.
View 5 Replies
View Related
Feb 5, 2014
How to combine many criteria in a COUNTIFS formula with the same range.
I made a formula:
=COUNTIFS(brut!$B:$B;"1T";brut!$G:$G;"F25"; brut!$C:$C;"1")
And it works fine, but I need four more conditions -
brut!$C$C; "2", brut!$C$C; "3", brut!$C$C; "4" and brut!$C$C; "5".
When I change the formula to
=COUNTIFS(brut!$B:$B;"1T";brut!$G:$G;"F25"; brut!$C:$C;"1"; brut!$C$C; "2"; brut!$C$C; "3"; brut!$C$C; "4"; brut!$C$C; "5")
It didn't work anymore.
I don't know how to use IF and COUNTIFS together.
View 4 Replies
View Related
Aug 13, 2009
I'm trying to use this formula in a cell but it keeps giving me #NAME.
What does this mean?
View 8 Replies
View Related
Feb 11, 2010
check out the COUNTIFS function on Data Tables Sheet -> Cell D29 and pls. let me know what's going on. I need is : To calculate the number of items on system data tab which has the "Pending" status and items which belongs to month "Jan-10" under a particular person selected from B 27 cell (Data tables tab).
I solved it by using >=01-01-10 and <=31-01-10 criteria but i don't think its nice idea .... I tried to use AND(function by got failed ...........
View 4 Replies
View Related
Jul 17, 2014
I need to count certain data if they match the same week number but they are not highlighted in red (manual colour not conditional formating). I have a macro that allows me to count cells with a certain colour but I cannot seem to tell it to only to count them if it also meets the criteria of relating to the same week.
Can i somehow combine these to functions or do I need to add soemthing to the countifcolour macro?
View 8 Replies
View Related
May 20, 2014
I would like to count the number of values in column B IF the value in Column A is equal to a value from a range of cells.
I would like the formula to be in cell E7. And the range of cells containing the values is in column D.
Here is my workbook Book1.xlsx
View 1 Replies
View Related
Apr 14, 2014
how to add/remove criteria to a COUNTIFS statement, based on the value of a VBA UserForm Checkbox.
All of the criteria for the checkboxes are based out of the same column. Here is the similar code I currently utilize for my checkbox filter.
[Code] ..........
The code hides fields within a collumn based on the tag within the checkbox's properties. Now I am trying to create an addition that will add criteria to a CountIf formula in real time.
View 14 Replies
View Related
Dec 2, 2008
I have the following formula in a sheet to record basketball stats that I am putting together.
=COUNTIFS('Running Game Log'!A:A,"=Jarod",'Running Game Log'!C:C,"=2 pt miss")
This formula counts from a list that is generated during in-game recording of stats & obviously counts the number of times "Jarod" misses a "2 pt shot".
My question is can the criteria "Jarod" refer to a value in a cell, rather than the specific name "Jarod" as I want to be able to change player names easily, rather than have to edit this formula every time.
View 7 Replies
View Related
May 10, 2014
I know this should be straightforward, but just can't "hack the logic", whether using "Countifs" oor "SumProduct" formulae.
Attached file 140509 String Criteria.xlsx file has database of three fields (Date, Route number and Part number)
I need the formula that totals number of rows meeting three criteria:
(a) A specific date from Col A
(b) A specific number from Col B
And the part I can't sort out:
(c) Any cell containing a "string" in Col C (so needs to count the "core" even if it has a prefix or suffix).
E.g. How many rows have "Date x" in Col A, "Route Y" in Col B and a cell containing "123" in Col C?
View 2 Replies
View Related
Feb 26, 2014
Need to use CountIFs formula to evaluate multiple conditions in the same column. Here is the formula I tried. It works for Resolved but returns 0 when i try to add Duplicate.
=COUNTIFS('P12 Source'!H:H,A18,'P12 Source'!F:F,"Resolved,duplicate")
Also used this formula with success by referencing a cell that had "Resolved" in it but when I tried to add another cell with "Duplicate" it again returned 0.
=COUNTIFS('P12 Source'!$H:$H,A19,'P12 Source'!$F:$F,'P12 Source'!$F$75)
View 8 Replies
View Related
Feb 16, 2014
I am trying to populate some results using countifs formula with multiple criteria. below is the formula am using and the last two criterias are dates
=COUNTIFS('Unproccessed CIT'!$B$5:$B$411,Summary!$A$4:$A$35,'Unproccessed CIT'!$G$11:$G$417,"<>"&"closed",'Unproccessed CIT'!$A$5:$A$38,">="&$C$2,'Unproccessed CIT'!$A$5:$A$38,"<="&Summary!$E$2)
Results:
#VALUE!
View 3 Replies
View Related
May 23, 2013
I'm trying to use countifs on a filtered table to find the sum for a column based on 2 criteria.
I've used =SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet3!K7,ROW(Sheet3!K7:Sheet3!K7:K20000)-ROW(Sheet3!K7),0)),--(Sheet3!K7:Sheet3!K20000="Yes"))
for a single criteria which is great, but I also need it to look at column B.
In a non-filtered table it works as:
=COUNTIFS('Sheet3 '!$K:$K,"Yes",'Sheet3 '!$B:$B,B5)
Is there any way to do this?
View 3 Replies
View Related
Aug 3, 2013
I have been using CountIfs with a lot of luck until I added one extra criteria that it ignores.
ORIGINAL FORMULA
=COUNTIFS(ADate, ">" & N$2, ADate, "=" & 10000, WBid, "" & N$2, ADate, "=" & 10000, WBid, "
View 1 Replies
View Related
Dec 28, 2008
I need to look at a series of data once per week and calculate the results. I'm trying to use COUNTIFS to get a result based on both the date I'm doing the analysis for and for the response.
I've attached a sheet that shows a sample of what I'm trying to do. Cell B1 contains the date range I'm using, and cell B18 shows the formula I'm having trouble with. I suspect it's because I'm trying to treat a date like text, but I'm not sure.
View 8 Replies
View Related
Sep 21, 2009
I need a COUNTIFS function (I think that will be the best function to use at least).
I have a two columns of data, column one is dates (mm/dd/yyyy) and column two is states (AK, MA, MI, etc). I need a way to count how many times a states comes up for a certain month. For example, I need to know how many times MA appears through the dates 1/1/2007 and 1/31/2007. Here's what I have so far. It works but I can only specify one date. =COUNTIFS('SHEET NAME'!D:D, "1/5/2007", 'SHEET NAME'!E:E, "MA")
View 5 Replies
View Related
May 26, 2014
Count the number of occurences on 1 day within a 60 minute timeframe. data includes dates and times
View 5 Replies
View Related
Jul 8, 2009
I want to count the number of occurences involving 3 columns of a spreadsheet, but one of the columns is a date/time field, and I only want to analyse the date.
Example:
A B C
John 1 02/07/2009 09:45:10
Peter 1 03/06/2009 10:14:12
Fred 1
Peter
John 1 02/07/2009 09:45:10
How can I ask how many occurences of John are in column A, with a 1 in column B, with the date 02/07/2009?
View 9 Replies
View Related
May 4, 2009
I have a sheet which details specific card numbers in Column A, and the date and place of transaction in the Column B. This very long list contains multiple entries for each card number. What I would like to do is use Sheet2 to list each card number and the number of times it appears in the list.
View 4 Replies
View Related
Mar 4, 2014
How can i count "how many "A" for period date in table and date out table in this case"
Date inDate outresult Count
05-Mar08-MarA ?
05-Mar09-MarB ?
06-Mar07-MarA ?
07-Mar08-MarB ?
07-Mar08-MarB ?
08-Mar09-MarC ?
05-Mar08-MarA ?
05-Mar08-MarA ?
View 3 Replies
View Related
