CountIfs Formula Where One Criteria Is String Within Cell
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
ADVERTISEMENT
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
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
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
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
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
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
Jul 17, 2013
I'm attempting to clear the contents from a range of cells on rows where a cell string may equal R, X, XX, Y, Z, ZX, #N/A.
The macro runs fine until it gets to a cell that contains #N/A. How to get this to work?
Sub Recalculate()
Dim r As Integer
r = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For Row = 13 To r
[Code] ........
View 2 Replies
View Related
Jul 1, 2014
I have a Countifs formula that is returning a zero "0",
=COUNTIFS($B$4:$U$4,$A29,$B$10:$U$10,B$18), and it should return a 1.
The manual calculation is turned on and I have tried the F9 and no change in the value returned.
View 2 Replies
View Related
Jun 23, 2014
I'm trying to count the rows of a worksheet based on the location selection. Heres what I'm using:
[Code]....
Before I add the Vlookup, I get a number of 50. I should get at least 40+, but after adding the
[Code] .....
I get "0."
I'm wondering if I need to use VLookup differently in this case ????
View 14 Replies
View Related
Mar 3, 2014
I have a problem with a cumulative countif and offset formula that I am unable to solve. See the attached spreadsheet for an example of the dataset I get out from our systems.
I want the formula to be fully automatic, so when I change the month in column K2, it will automatically count all the different outlets in column B based on criteria in column L3 and downwards. The dataset will be updated monthly obviously.
I believe that it could be solved with the usage of a countifs + offset formula or something similar.
The main idea of the spreadsheet is to track the movement of customers between different chains. It is thus important that customers that show up in January will not be counted again in February or March, thus creating an unique countif list based on a given month.
Number of outlets example.xlsx
View 12 Replies
View Related
Oct 6, 2008
I am having trouble converting the following Countifs formula developed in 2007 to work in 2003 Excel.
Basically, I want to count the frequency of "N" in 'Data_Q2 2008'!$S$2:$S$9806, where 'Data_Q2 2008'!$B$2:$B$9806 = 'Metric Upload'!C2.
'Data_Q2 2008'!$B$2:$B$9806 is a list of physicians. 'Metric Upload'!C2 is a pick list for selecting the physican that is in the larger data set using Data Validation.
View 14 Replies
View Related
Jul 23, 2013
I currently have 6 formula (see below) that I want to add another variable for. The formulae add together letters in B1:B15 cross referenced against numbers in A1:A15. The first formula adds together the A's and O's in B1:B15 and cross references them with with cells beginning with 5 or 6 in the same row, A1:A15 down.
=SUM(COUNTIFS(B$1:B$15,"A",A$1:A$15,{"5";"6"}&"*")+COUNTIFS(B$1:B$15,"O",A$1:A$15,{"5";"6"}&"*"))
=SUM(COUNTIFS(B$1:B$15,"B",A$1:A$15,{"5";"6"}&"*"))
=SUM(COUNTIFS(B$1:B$15,{"A";"O"},A$1:A$15,"4*"))=SUM(COUNTIFS(B$1:B$15,"B",A$1:A$15,"4*"))
=SUM(COUNTIFS(B$1:B$15,"A",A$1:A$15,{"1";"2";"3"}&"*")+COUNTIFS(B$1:B$15,"O",A$1:A$15,{"1";"2";"3"}&"*"))
=SUM(COUNTIFS(B$1:B$15,"B",A$1:A$15,{"1";"2";"3"}&"*"))
I want to cross reference each of these with a Gender variable (denoted as M or F, for male & female) in the C1:C15 range. Using the last formula as an example should tell me how many M's or F's in the range C1:C15 had B and began with either 1,2 or 3.
View 1 Replies
View Related
Dec 3, 2013
I have a query from an access DB that I pull into an excel sheet automatically every 15mins. Column G contains a list of 6 digit customer reference numbers and column C contains a list of the users that have modified records for that customer.
I use a summary sheet to count the number of customers a user has modified records for in that day. The trouble I am having is counting unique records using the COUNTIFS function. In my database if a user changes a record and hits save 10 times that will create 10 records. I only want to count this as 1. I am using the following formula but it doesn't seem to like the 2nd range and criteria in the countifs bit.
=SUM(IF(COUNTIFS('Daily Loss Data'!G2:G1500,'Daily Loss Data'!G2:G1500,'Daily Loss Data'!C2:C1500,"John Smith*")
=0, "", 1/COUNTIFS('Daily Loss Data'!G2:G1500,'Daily Loss Data'!G2:G1500,'Daily Loss Data'!C2:C1500,"John Smith*")))
Daily Loss Data Sheet Notes: Column C = user, Column G = Customer Ref.
I am using an IF statement because my range changes after each query refresh so contains zero's that I obviously do not want to count.
View 1 Replies
View Related
Jun 21, 2014
Have table with five columns and five rows. The columns(C4:H4) hold Systems, Systems is a defined range, and rows (B5:B9) hold staffer names, Staffer is a defined range. Cell A5 holds period, by quarters, ie, Q1-Q4.
At present am using =COUNTIFS(Staffer,$B$5,System,C4,Q,$A$5). This works fine but as you can see I must change the Staffer starting reference $B$5, with each new row.
formula that changes column and row automatically as I drag down and across.
View 6 Replies
View Related
May 8, 2009
I'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.
So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15.
Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.
How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).
As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.
The answer I need is 2 because, discounting the zero value, 126 is the second highest value.
View 9 Replies
View Related
Mar 17, 2014
I have attached a sample workbook which shows what I am trying to extract from sheet "Service Reminders 2014". In worksheet "Results" cells b11:e11 I am trying to extract the amount of vehicles with within age ranges provided that have a magic number attached which is pretty easy everything >0 is a proper magic number and also the amount of vehicles in each age range that have "Booked" associated within the range of "Service Reminders" Y2:AH5000, The formula works fine until I add the final criteria and then it give me a #VALUE!
View 14 Replies
View Related
May 1, 2012
I have a large metrics spreadsheet that has thousands of formulas using SUMPRODUCT. We are using Excel 2007/2010 now and I wondered if there was a way to convert these type of formulas to use the SUMIFS or COUNTIFS. I can do simple ones with 1 or 2 criteria where its AND, but then there is a combined OR and AND like this one has I cannot get them to work right, am I stuck with using SUMPRODUCT? My hope was to speed up the overall calculation speed. Note: The data spreadsheet 'Sheet2' contains 64555 rows, I write these formulas using VBA so that the range is finite to the actual data range is why you see 64555.
=SUMPRODUCT(--('Sheet2'!$J$3:$J$64555=$A6),--(('Sheet2'!$I$3:$I$64555="HOC")+('Sheet2'!$I$3:$I$64555="MGN")))
View 2 Replies
View Related
Jan 22, 2014
I have attendance tables that has column Y showing the shift to be worked and column Z showing any absentees. I used the following formula to count the number of people off per period.
{=SUM(COUNTIFS($Y$5:$Y$29,AM,$Z$5:$Z$29,$W82),(COUNTIFS($Y$36:$Y$60,AM,$Z$36:$Z$60,$W82)))}
in the above formula AM relates to a list of morning shifts.
The problem I have now encountered is that the system at work is still using excel 97 - 2003 which does not recognise Countifs.
way to adapt the formula to be compatible with Excel 97.
View 7 Replies
View Related
Apr 18, 2014
Using COUNTIF/COUNTIFS how to counting data with 3 mode ;
name
property
checking
[Code]....
I want to count with criteria based on adjacent value "name" column related with "checking" column
1) counting data "name" with "yes" criteria?
2) counting data "name" with "yes" & "no" criteria?
3) counting data "name" with blank "" criteria?
4) counting data "property" with criteria contains "name" and "yes" criteria
View 9 Replies
View Related
Apr 25, 2014
Because countif cannot be used across multiple tabs, I'm using the following workaround where "MySheets" is the range of tabs and E8 is the cell I want "counted".
=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!E8"),"Fully implemented"))
This works perfectly, except that I really need COUNTIFS... I want Excel to count either "Fully implemented" or "Partially implemented" when in E8. I cannot get the formula to work and keep getting an error message.
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&MySheets&"'!E8"),"Fully implemented",(INDIRECT("'"&MySheets&"'!E8"),"Partially implemented")))
View 1 Replies
View Related
Apr 22, 2014
I am trying to count how many work orders we have created from 3 months (90 days) ago. In an effort to make the input work minimal. I wanted to use the =Today() formula and then subtract 3 months.
So I currently have =Today() in A1 and then (A1-90) in cell B1 and I get the date I want.
However, When I use a countifs formula and my criteria is
View 1 Replies
View Related
Apr 3, 2014
For whatever reason, I don't succeed to write a formula with a string to a cell using vba The char(10) is not accepted
Code:
LimitGainLong_.Formula = "Limit" & CAR(10) & "Profit: " & LimitProfitLongGain
View 5 Replies
View Related
