Reconciling Countif
Oct 7, 2008
I have a spreadsheet containing 465 rows of data. Each question is tied to a subtopic. I've used a countif formula to count how many times I'm using all of the topics. I come up with 491 uses of all subtopics. How can this be if I only have 465 questions? Each question is only tied to 1 subtopic.
I've checked every countif formula and it is indeed giving me the correct number. ex: aprs is used 3 times, pricing request is used 13 times, etc. they are all correct.
Why won't the total number of questions match the total number of times a subtopic is used?
View 9 Replies
ADVERTISEMENT
Jul 23, 2012
I have four worksheets and they are named: P (the master spreadsheet), and then A, G, and S (which, combined, should contain all the values on P). These spreadsheets are identically formatted. Column A in each spreadsheet has a Name, and column B has a quantity associated with that name. There are no column titles or headers.
Basically, I need to ensure A, G and S role up into P.
I'd like to create a macro that will identify instances where Column A (the Name) on the master spreadsheet has a match with Column A (the name) on any other spreadsheet. Then when there is a match, I want to reconcile Columns A (the names) and B (the quantities) on the master spreadsheet against its corresponding values on the other spreadsheets. In my ideal scenario, a new worksheet would be created showing:
Column A: Contains the values from Spreadsheet P, Column A
Column B: Contains the value from Spreadsheet P, Column B
Column C: Blank
Column D: Contains any matching value to Spreadsheet P, Column A, but otherwise states "No Match"
Column E: Lists the corresponding value to Column D, but otherwise states "No Match"
Column F: Equals Column B minus Column E, but otherwise states "No Match"
I've tried applying examples from other threads but they are so customized/specific to people's individual needs that I've been unable to make them work for my more simplistic example above.
View 1 Replies
View Related
Mar 13, 2013
I have a dataset from Oracle that includes a study patient ID, study visit that a UC sample was collected at, date the sample was collected, and sample type. I also have a dataset from Quest, where the UC samples are sent to for testing, that contains similar information.
I need to reconcile this data, making sure that patient IDs, study visits and collection dates all match up with out using just vlookups and having to check the reverse of each (vlookup Quest into RDC, then vlookup RDC into Quest) and I've been wracking my brain trying to figure out VBA code to get my data from Oracle (orange header) to merge with Quest (blue header) and merge on Subject ID and Visit Name only.
In exhibit C, this is what ideally I would like to see as a result, data points from both are reported, but if Oracle subject id = Quest subject ID and Oracle Visit Name = Quest Visit Name, they are reported in the same row. I can then just run a simple formula to compare the collection dates and also identify discrepancies between visits (example: exhibit C, rows 10 and 11, these two UC samples look like they should reconcile because the collection date is actually the same for both, however either the study site or Quest is reporting the wrong study visit name).
View 9 Replies
View Related
Nov 12, 2013
I need a little coaching on big data. I have two reports, one from a production system the other from the invoicing system. I need to reconcile the two reports to ensure our invoicing ties out to production. At this time I am focusing on 1 particular item code, but there are potentially hundreds of item codes.
I have 1 Item Code.
I have a around a hundred invoices. (Sample 100)
I have a few hundred different customers. (Sample 100)
I have several hundred lines of billing data. (Sample 650)
I have several thousand lines of production data. (Sample 40,000)
In the billing system, per invoice I might have 1 or more billing lines for this item, usually no more than 2.
In the production system, per invoice I might have 5 to 50 or more production lines for this item per invoice.
Example
Prod System:
30 lines of data has a run amount and a total run amount for each line.
I am looking for tools to reconcile this data. VLookup and Index/Matching is not cutting it. VLOOKUP does not move beyond the first line found. Index/Matching wants to add all the prod together. I can get subtotals easy enough. I have tried Concatenation, but when it takes multiple production data lines to equal 1 billing line, this does not work. I needs some thing that can look at the Qty on a billing invoice and tie it out to various lines of production data to identify which production jobs went on that line of the invoice.
Here is a sample. These are fairly easy to resolve, but others are not so easy due to number of production and billing rows.
Invoice number
Billing item
Est. Quantity
Actual quantity
Billable Qty
Sales order
Position number
Sequence
Agreement
Billing source
Description
Total Billable Qty
[Code] ..........
View 4 Replies
View Related
Apr 21, 2009
I have a simple database spread sheet and I need to count a column under certain conditions. In one column I have employee names that appear repeatedly, in another I have codes. I want to be able to count how many times the code appears next to the name.
For instance:
If b4:b65000 = Sam Douglas then I want to count how many times different codes appear in the adjacent cell.
Sam Douglas:BI
Sam Douglas:BI
Sam Douglas:SI
Sam Douglas:BI
BI = 3
SI = 1
View 6 Replies
View Related
Jul 14, 2009
I have following data (two columns Parent and Child), now I want to apply Countif on Child cell.
But in Countif I want to provide the criteria...let say only count those childs whoes parent is A.
How to do this in Excel.
Parent Child
A e
A f
B g
B h
B i
C j
C k
View 12 Replies
View Related
Aug 11, 2009
I'm reasonably new to Excel, and have a fairly basic question to check out:
I have been using the COUNTIF function to count up numbers of items in various categories in a column.
The formulae I have been using are like this:
=COUNTIF(F$3:F$201, "Red")
or where I've wanted to combine various comments
=SUM(COUNTIF(F$3:F$201,"Yellow")+COUNTIF(F$3:F$201,"Cream"))
I'm not sure what formulae to use to count up
1) the total number of entries in that column, so that I can make sure that I haven't missed some (without having to check manually!)
2) how to count up the values that do not match the other categories that I have specified in the COUNTIFs: this would be a value for finding how many 'other' entries there are in that column, without having to specify those values
View 11 Replies
View Related
Jul 6, 2012
I am trying to use COUNTIF with two critera. If this isn't possible is there any other way possible of doing this in a range of cells.
What I am trying to do is show the amount of students in a year group who spend x amount of hours on the internet and have a target grade (for example) of Lvl 4
I have been trying use a formula along the lines of =COUNTIF (Q5yr7, "0- 1Hour", Q12yr7, "4")
View 9 Replies
View Related
May 14, 2014
important columns are: "D", "G", "L".
this is about a project is a granite to be installed in 104 units, these units are divided in 4 types (column "L") K1, K2, K3, K4
I tried IF, SUMIF, COUNTIF, ETC but I think that I need is some combined function that I'm not able to figure out.
as you can see in the attached file, my units 101, 102, etc is not been installed yet (column H is empty), but the units 209, 210, 211, 214 yes was installed on 5/6 for example.
for example, if i use =COUNTIF(H4:H20,">0"), will return me all the H that is not empty (=3), how many kitchen was installed so far, but, I would like to know how many of these returned units (3), is my different types: K1, K2, K3 and K4....
View 5 Replies
View Related
Aug 1, 2014
I am trying to count the status and type of some work so:
Column A would contain the status of the work e.g. open, in progress, closed etc.
Column B would contain the department: ict, development, operations, etc.
I want to do a summary that shows: How many are in ICT are open, closed etc.
I can do a countif to get the total open, in progress etc or total number of ICT jobs but not ICT In progress.
View 1 Replies
View Related
Dec 14, 2013
What i want to do is count but only if another cell meets a certain criteria,,
Let me explain better,,what i want to do is count the number of 'Completed' cases by each individual, i.e. count if the cell A1 = Completed and B1 = Kevin and the same for 'Pete'
I'm sure i need some sort of nested =IF AND formula but just can't think how the nesting should look.
B
CompletedKevin
CompletedPete
CompletedPete
Parked Kevin
SubmittedKevin
CompletedKevin
Started John
With AdviserPete
With AdviserPete
Started John
Started John
View 4 Replies
View Related
Dec 17, 2013
I have attached a copy of my spreadsheet .My formula is in column BA and I am trying to figure out which students have 5 A* - C. We are only allowed to add 2 vocational subjects to this formula so basically 3 (or More) GCSEs + 2 BTEC/OCR Nationals. Now my formula works pretty well except if the candidate has 4 GCSEs and 0 or 1 BTEC it is still calculating as though the candidate has achieved 5 A* - C.
I have highlighted GCSE Subj in pink and Vocational Subj in Puple.
View 5 Replies
View Related
Jan 27, 2009
I've been asked to extend the counting capabilities of my interface to pick up the profession of each person when they answer whatever is selected in the 'Interface' worksheet (cell B2) put them in my interface.
I've been trying various incarnations of SUMPRODUCT along with what DonkeyOte helped me with before, but currently no joy. Hope that makes sense - have a look at previous post for further details. I've attached an example to look at.
View 2 Replies
View Related
Feb 3, 2009
- Column D includes dates.
- Column E includes text, either "Yes" or "No".
I want Excel to count all cells in Column D between 1-Jan-2009 to 31-Jan-2009 + any cells that say "Yes" from Column E on the same row.
View 5 Replies
View Related
Apr 14, 2009
I'm trying to count cells in one column that match a variable only if it also matches a variable in another column. For example, I want to count all of the cells in column A that match "Franklin" only if column D shows "True".
View 5 Replies
View Related
Apr 21, 2009
I have a formula =IF(AND(F14=3,G14<2)=TRUE,1,"") which is in cell J14. I also want to 'add' to this formula an 'or statement' (I am not sure of the terminology, so taking a shot in the dark). So it would continue to complete the calculation as normal, but if there is a '1' in K14 it would also output in J14 as a 1
View 2 Replies
View Related
Jul 2, 2009
I have a worksheet where I am trying to count the number of occurences of several text strings.
For example:
I'm trying to count how many times "paid in full" and "fully paid" occur in column A.
I have two formulas, and both seem to work, but since I don't really understand either of them, I'm wondering which I should use and how I would adapt it to include additional text strings. (Like adding "paid" to the list)
Here are my formuals (I didn't write either of them, another co-worker did)
=(COUNTIF(A:A,"paid in full"))+(COUNTIF(A:A,"fully paid"))
=SUMPRODUCT(--(A1:A50={"paid in full","fully paid"}))
Also, if there is another and easier way to do what I'm trying to do, I'd love to know.
View 6 Replies
View Related
Sep 18, 2009
I have a table with 2 columns - Gender & Age. Now, suppose I want to know the number of Males in the table with age greater than 14, how do I do that? Can a Countif formula be used for more than one condition or is there some other formula?
View 4 Replies
View Related
Dec 1, 2009
I have two different worksheets.
One has dates recorded as dd-mmm-yyyy (e.g., 28-Nov-2009).
On the other, I have to create a lot (over 1000) of countifs against the months/years of these dates. So, for example, if there are several dates on the first workbook that fall in Nov-09, i want to do a countif on the second workbook that tells me how many Nov-09 dates there are.
View 5 Replies
View Related
Jan 27, 2010
My boss has asked me to work this one out and is putting me under great pressure to resolve it. I have tried Vlookup and COUNTIF etc but just cannot get to make it work. I have attached an example file but to explain:
I have a database of inspectors faults, in column B, I have the week number, column*C, I have the part number & column C the fault category (this includes OK). The actual database is up to 5000 rows now.
What I need to be able to do is in one cell (column H2) count all the OK's for week 1 and all the Not OK's (column I2) in week 1 and the same for week 2 (column H3) and week 3 (column H4) and so on for the whole year. I have tried a vlookup using cell G2 (containing "1") as the search and the week number column as the range and then counting if OK, but it never worked!
View 2 Replies
View Related
Feb 11, 2010
I am trying to create a COUNTIF formula which will work with two conditions. If you see the attached spreadsheet you will find the data that I am trying to apply a formula to. I have my data in the table on the right. The table on the left is supposed to show the number which the number of destinations that had a certain range of visitors.
As you will see that there are 3 destinations that had 12 or more million visitors, this was counted using a basic COUNT IF formula but for the rest of my data how can I apply the formula so that the correct number of destinations are counted. For example what formula would be needed to count the number of destinations that have had 8-11.9 million visitors. I am guessing that the formula will have the conditions ">=8" and "<=11.9"?
View 3 Replies
View Related
Aug 28, 2008
I have 2 ranges with values, and I want to use countif to see of there's a same value in each of these ranges.
View 9 Replies
View Related
Jun 13, 2009
This is a Cross-Post because when I tried to post this on the Mr Excel Forum, it did not work properly with the MHT files I used. Mr Excel Forum requires an add-in to Excel called: HTML Maker 2.42, however, I was unable to obtain HTML Maker 2.42 from the owner's website or any alternate websites. Plenty of people viewed my message but no replies. In the attached workbook named: Test 2.xls please view the worksheet named: Summary. On this worksheet the user (user has very little experience in Excel 2003) selects an Operation in cell B2 via a drop-down list and a Task Group in cell B4 via a drop-down list.
After the user has made their selections, I need Excel 2003 to automatically Select worksheet named: Data. I then need it to automatically filter the user selected Operation in column B and the user selected Task Group in column C. Once the filtering is complete, I need Excel to automatically calculate the COUNTIF functions as follows: on columns E and U and populate the results back on to the worksheet named: Summary as follows:
Cell Summary!B8: =COUNTIF(Data!E$3:E$65000,"PERMANENT")
Cell Summary!B9: =COUNTIF(Data!E$3:E$65000,"FUTURE")
Cell Summary!B10: =COUNTIF(Data!E$#:E$65000,"TEMPORARY")
Cell Summary!B11: =COUNTIF(Data!E$3:E$65000,"FUTURE DELETION")
Cell Summary!B25: =COUNTIF(Data!U$3:U$65000,"Y")
Operations has a defined name = Operations!$A$2:$A$12.
Task Group has a defined name = Groups!$A$2:$A$29.
View 4 Replies
View Related
Oct 16, 2009
Is ther any way around not being able to do this - I read that if u make the ranges an array it shoul work - Shift, Control, Enter - or something but I can get it to work. I was hoping to use copuntif for this :-
COUNTIF(F33:F39,M33:M39,T33:T39,AA33:AA39,AH33:AH39,AO33:AO39,F67:F73,M67:M73,T67:T73,AA67:AA73,AH67 :AH73,"b")
View 5 Replies
View Related
Apr 25, 2006
i need to know, how much people belongs to the number in Colum A - if in
colum C is written "ISM".
A B C
1 1 Meier ISM
2 3 Huber ISM
3 2 Schmitz UPA
4 2 Mayer ISM
5 1 Mueller UPA
6 1 Hase ISM
View 12 Replies
View Related
May 27, 2006
my current formula is =COUNTIF('Input Page'!A2:A50000,"=Monday")
i'd like to change it to check what day is in the field and then only do the above formula if that day is within the past week.
so i need the "=Monday" section to be changed to read "(is equal to monday) and (is between today and today-6)" ...todays date will be taken from 'Input Page'!B2:B50000
View 11 Replies
View Related
Sep 1, 2008
I am trying to count values in cells of column A only if there is something (any value) in corresponding cells in columns B, C, D, and E. If there are no values in cells of columns B, C, D, and E do not count the cell in column A.
View 10 Replies
View Related
Nov 5, 2008
See attached document, there are 11 cells in which will either contain Yes or No. Looking at the different combinations that there can be there can only ever be 9 out of the 11 cells being used or 10 out of 11 being used.
Also the last question (Row 25) could be filled N/A if this occurs I would like the formula not to count that. Is there a counting formula or IF formula which can be done to help me out?
View 12 Replies
View Related
Jan 3, 2009
I have 16 columns with 10 rows with different single digits in them. I want to count the number of times the number 2 appears in columns A, C, E, G, I, K, M, O (in other words every other column in this case).
I know how to write the formula by using countif to find the results but it is rather long. The fomrula would look like this:
View 5 Replies
View Related
Mar 30, 2009
If I have column A with a department name e.g. A, B, C, etc
and
column B with a date next to the some of the entries of column A.
How would I compose a formula to return an answer of the amount of records that show column A as "A" and column B as "1".
This is probably immensely simple and I have a wet fish on standby to hit myself with.
View 6 Replies
View Related
