i need to count based on 2 seperate sets of criteria in the same row.
column G houses the total number of hits as a number,
Column E holds the division
Column B holds the job title,
rows 2 through 222
i need to sum up the numbers in each G field, where the G and E field match a specific text criteria.
I've got several sets of data all which link to seperate charts, the problem i'm having is that i dont want the chart to display the fields with zero values and with the amount of charts and data it is taking ages to re-source the data is there a way of getting the chart to ignore zero fields or of somehow sorting the data in a way only fields with values are shown
View 9 Replies View RelatedI'm using a sumproduct forumla to count rows based on specific data in multiple columns. So if column A equals 1 and column G does not equal 6 and column M equals 4 then count that row. I know how to do this. The problem I'm having is that I want to count column A if equals 1 or 2. I tried adding an OR comand in with my sumproduct but it doesn't seem to work that way.
View 4 Replies View RelatedI have to look through an entire table and find certain criteria (eg. "STEVE'S PIZZA SHOP"). It might be in columns G or H, or it could also be in Columns C or something of that nature. What I'd like to do is search through the table, find each instance of said criteria and cut each row the criteria apperas in to a different sheet. I'd like to do this as a macro, so I can set it up for other criteria as well. In addition, if I can include in the macro a way to create a header row (which I'm pretty confident I can myself), as well as change the title of the sheet the information is moved to. I have looked up Do-While loops, For-Next loops, If-Then loops. I am at a total loss.
View 9 Replies View RelatedI need to copy all the work sheets into one single work sheet (mastersheet). The source work sheets are having same column structure. The condition which i need to take care of is that after column 3 if at all there is any data till column 10 then in the destination mastersheet these should be copied in different rows with first two columns repeated. I need to do this using VBA macro.
View 9 Replies View RelatedI need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.
Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.
Book1.xlsx
I am trying to count AE2:AE601 if AE2:AE601 <= 2 and if AF2:AF601 = the value of AH2 but the formula that I have create returns a 0 when it should be returning a 1.
Where did I go wrong? Here is what I have so far.
=COUNT((AE2:AE601)*(IF(AE2:AE601,"<=2",IF(AF2:AF601,"="&AH2))))
This is probably a simple question for excel gurus, but I am a molecular biologist, and don't set up these formulas every day. I have a table full of genotypes that I have indicated as 0, 1, 2 or 3 in columns. Column heads are gene names; row heads are subjects (patient numbers). I want excel to look in row 1 in column A and see if there is a 1 and also in column B in the same row to see if there is a 1, and if both are 1, count that patient in a tally. I want to be able to get a count of the number of patients with 1's in both columns. If someone can tell me what functions I use to set up this tally, I think I can set up more elaborate formulas. At the end, I want excel to look at several columns and tally the number of patients that have 1's in several columns.
View 3 Replies View RelatedI have a spreadsheet for my customers. i Break them down in 3 categories
L c r Column o gives me the total dollar amount of all columns. I have figured out how to tell how many of each I have sepratly and there respective dollar totals
How do count how many $0 amounts in column o belong to l r c respectively
In other words if I had 10 c's how many were $0 in o. Lets say I had 10 c's but only 6 had a total greater then $0 in column o that would be 60 percent
I am looking to count every 4th column that has a average score less than 90. My columns are set with 3 scores and 4th column has the average of those scores. Like such:
untitled.jpg
I can count them using a SUMPRODUCT(--(MOD(COLUMN function but I cannot get a criteria to work.
Non-VBA preferred
Attached is a workbook that better explains what I am trying to do. I am counting pastdue accounts from several different insurance companys. I have several pastdue brackets, and I need the total amount of pastdue accounts for each bracket, for each insurance company. I think the countifs function wrapped in an index match may get the job done, but I have not been able to figure it out. right now I have a countifs wrapped in an if statement, and it is producing incorrect results.
test book 1.xlsx
I am attempting to count records based on 3 criteria in 3 different ranges and am hitting a wall. My formula will be on a different worksheet as it is the summary sheet.
Worksheet "Cases"
Column E - Type
Column F - Month
Column G - Year
Worksheet "Recap"
Recap month will be input into Cell I5 eg. "6" for June
Recap year will be input into Cell K5 eg. "2009"
Cells N10-N15 is a list of Types
Summary Cell J10 will need to count if
1)worksheet Cases column E = worksheet Recap N10
2)worksheet Cases column F = worksheet Recap I5
3)worksheet Cases column G = worksheet Recap K5
I've looked at the various examples in a search of topics simillar to my topic but I still can not get thsi to work.
I have a work book that contains 2 sheets
Sheet1 is called RawData of Multiple columns
Sheet2 is a quick report basded on Sheet1 data.
I need to find a way to do counts where 2 criteria are met.
Example.
Count the number of Times the phrase Major Victory appears in column M
( Range M1 to M50 ) of Sheet1 IF the value in Column L ( Range L1 to L50 ) of Sheet1 = USA
I tried this:
=COUNTIF(RawData!M1:M50,B1( AND(RawData!L1:L50=$A1)))
I am using B1 and A1 ( from Sheet2 ) as search criteria
but it gave me 0 ( and I know this is wrong ).
Based on the reading I did on simillar topics it appears that I cannot use CountIF in this case.
I have ahseet with the following columns:
Col 1 = Item Description
Col2 = Price
Col3 = Discount Rate
I would like to gather a count on another sheet of all instances in col 1 where it is equal to 'Fridge' I know I can use the SUMIF function but I would like to do it in VBA code.
So here is my little dilema,
I have a worksheet that I need help with using SUMIF function.
Basically how do i use this function to search for 2 sets of criteria as opposed to just 1.
I have been working with personal record data in multiple workbooks, using sumif, index, vlookup etc to show various required info.
What I now need to do is take all the records of people aged between 16-18 and list this in a new worksheet.
I can get all the records I need in a pivot table but it needs to be something I can add columns to in order to gather further info.
What I require : Column F to be blank as long as the following is true:
1) Column A (Order) cannot be blank.
2) Column E (Status) has to be equal to either "Completed" or "Successful".
3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.
In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.
WORKAROUND.xls
count cells row wise that meets criteria both in column & Row as well.
View 2 Replies View RelatedI'm trying to create a formula that tells me the following:
If the item is marked as closed (vs. active), then how many renewals/terminations replacements/etc are there? These are two difference columns in a worksheet.
using various keywords, and I haven't been able to find a solution that works. It would seem SUMPRODUCT is what I need to use, but thru all the combinations I've tried, I still can't get the syntax right. The problem is: I have dates in col A, and the word "Holiday" in col D. I need to create a formula that counts the number of instances of the word "Holiday" in col D, only where the date in col A is =DATE(2008,1,1))*(A11:A45
View 9 Replies View RelatedI'm facing a big problem and I can't found a solution for days.
I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below:
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))
Let me Put to you an example for you been able to understand my problem.
Example considering Range "C2:C20" exluding blank cells
(1) - To count unique values
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))
(2) - To count unique values based on criteria in range "B2:B20"
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E"))
(3) - To sum unique values on "E2:E20" based on same criteria
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20))
They all work fine. But I need to insert a merge at Row 10 for
better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's.
I just don't know what to do guys ....
I need to have multiple ranges count based on several criteria. The hitch is that needs to count if one or more criteria is met not all. I tried using a COUNTIFS however that seems to evaluate based on AND not OR.
Is there a nested COUNTIFS or similar function that would work?
My data is like:
A1: 5 -- B1: X
A2: 5 -- B2: X
A3: 5 -- B3: Y
A4: 3 -- B4: Y
A5: 3 -- B5: X
A6: 8 -- B6: Y
A7: 2 -- B7: X
A8: 3 -- B8: Y
A9: 1 -- B9: X
I want the count of unique numbers in column A based on the criteria i have on column B.
So the count for X should be 4 and for Y should be 3..
Can i achieve this using formula only?
I have 2 columns on column contains 2 numbers, the other comlumn has names. We'll use A1:A4 and B1:B4 for the example.
Row A Row B
25 John Smith
50 Mike Jones
25 Chris Carter
50 Mike Jones
I want to be able to count one number in "A" but only if the name is not duplicated in "B".
My result for #25 would be 2.
My result for #50 would be 1, because Mike Jones is duplicated.
The results would be displayed in two seperate boxes, so I would only need on formula to count #25, then one formula to count #50.
I have two sets of data:
Data set #1
Indicator PriceIndicator Price Date
2.1 10/27/08
2.11 10/22/08
2.17 11/21/08
2.38 03/20/09
2.38 03/25/09
2.46 03/19/09
2.5 03/09/09
2.5 03/24/09
2.53 12/04/08
2.73 12/09/08
2.82 12/24/08
2.83 12/18/08
2.89 12/12/08
2.9 03/13/09
Data set #2:
Close Price Close Price Date s1s2s3s4r1r2r3r4
2.25 12/11/09
2.30 12/12/09
2.40 12/13/09
2.00 12/14/09
2.12 12/15/09
2.50 12/16/09
2.51 12/17/09
2.53 12/18/09
2.49 12/19/09
What I'm attempting to do is find what are the CLOSEST four indicator prices in data set #1 are above (r1, r2, r3, r4) and below (s1, s2, s3, s4) the closing price in data set #2. Also, I can only use the indicator numbers in data set #1 that are on or before the close price date in data set #2. Because of this, not all of the r's and s's will be filled in.
I would like to use just regular excel formulas, but I have a feeling that VBA may be my only option.
I posted an earlier question about using a countif formula with two sets of criteria, Below is the formula I was given but for the life of me I can not get it to calculate. I have created a drop down box for each agent at my work and a drop down box for pass and fail. I want to keep a running total if they pass or fail on an assigment. I have labeled my worksheet phone.
=sumproduct((Phone!G:G="Fail*")*(Phone!B:B="Smith, John*"))
I have a worksheet with two tabs.
First Tab
Account Name
Account Number
Second Tab
Account Name
Account Number
Account Ship Location Number
On the first sheet each account name and number only appears once.
On the second sheet there may be multiple entries for each Account Name and Number.
On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.
I can't seem to get a formula to work. I am trying to count the number of times "EE" shows up in a range only if "CR" is listed in that row within another range. For simplicity Column A has a list with the following values scattered from A2 through A100: CR, LT, ST, DI. Column B has a list with the following values scattered from B2 through B100: EE, EF, EG.
I need to know how many EE's are listed in column B if CR is on the same line in column A.
I get this work a lot and am looking for a much more effective way of working the dataset,
I have numbers like 1.1, 1.2, 1.3 and so on.......
These come under criteria like (1) = 1.1, 1.2, 1.3 and so on.....
I am look for an automated way of doing a count of any number that falls under this criteria, so I want to count based on criteria (1) it would count all 1.1,1.2,1.3 and so on as one count.
I am attaching a sample document to see how it is laid out. [URL] .....
I have the below table of data and what I am looking for is a formula that will count the number for unique numbers (col E) depending on a several criteria. So far I have got this formula but it's not working!
=IFERROR((SUMPRODUCT(--(A3="2014"),--(B3="1"),--(D3="Boc Limited"),)),"")
A
B
C
D
E
year
week
smsupplier_code
supplier name
orders_no
[code].....