Count Certain Phrase When There Is Multiple Answers

Jun 4, 2014

In the attachment, on the totals sheet I am doing a count of the results on Sheet2. Under "Alcohol as it Applies to Me" on Totals I am trying to count the 5 different categories, but the original question is a pick all that apply so at times there are multiple answers. I can't figure out the formula to count each phrase when there is multiple answers.

HRA Results.xlsx‎

View 2 Replies


ADVERTISEMENT

Return A Count From Multiple Columns - Each Column Has Multiple Answers

Sep 28, 2012

Want a single count of multiple columns based on the columns selected value. Data is in text format.

Have tried multiple COUNTIF statements and have tried using pivot table (Excel 2010) both only give me total counts for all. I think I need an OR statement somewhere, but not sure where?

In other words, if a single record has an "any" in the any fields or a "yes" in the yes fields, I want to to count that as one record.

Sample data:

Pegnancy Smoke
Pregnancy Alcohol
Pregnancy Marijuana
Pregnancy Powder
Stress Cigarettes
Stress Marijuana
Stress Alcohol
Stress Medication

[Code] .....

View 2 Replies View Related

How To Count Number Of Times A Phrase Is Repeated In A Row

Jun 24, 2012

How I can count the number of times each unique phrase in row "A" is repeated?

For example if my data set was

Blue
Green
Black
Green
Red
Red
Red
Red Hat

how can I get excel to count the number of times and return data like

Blue 1
Green 2
Black 1
Green 2
Red 3
Red 3
Red 3
Red Hat 1

View 3 Replies View Related

Count Up How Many Times A Particular Phrase Within A Text String Appears

Feb 7, 2007

i have a spreadsheet where I need to count up how many times a particular phrase within a text string appears. The text string will be duplicated many times throughout the spreadsheet.

For example :

Miss X was at work on Saturday
Mr XX was at work on Saturday but not Tuesday
Miss Y was at work on Tuesday

So I would like to count up how many times "work on Saturday" appears in my spreadsheet, and then as a seperate query, how many times "work on Tuesday" appears.

View 9 Replies View Related

Pivot Table - Displaying Count Of Data In A Column With Y/N Answers

Dec 16, 2013

I am new to Pivot Tables and I am having difficulty displaying a count of data in a column with Y/N answers.

Previously I would have undertaken this using the SumProduct function in a standard table.

I attach an example workbook with my data, what I want it to look like and the pivot I have created.

Book1.xlsx‎

View 4 Replies View Related

MAX Multiple Answers

May 8, 2006

here's the formula that I'm using: =INDEX($C$35:$AF$35,MATCH(MAX(C41:AF41),C41:AF41,0 )) where $C$35:$AF$35 contains names of people & C41:AF41 contains #'s. This works great if there isn't a tie. Is there a formula to search the range, find the max # and if there are two answers display both names. ex. the max# is 3 and both joe and sam have 3. it would then show joe / sam or something like that. Is this possible? If not, then if there is a tie for the max #, to have the cell just display tie.

View 4 Replies View Related

Vlookup And Multiple Answers

Jun 26, 2008

I have a table whereby I Vlookup a different spreadsheet on an order number (in this instance Z011352/001). There are multiple sketch numbers associated to this Z number.

I would like to be able to input the Z number and then obviously the Vlookup pulls up the first sketch number. This works fine.

If I then input the same Z number again it pulls up the same sketch number, but I want it to look in the current table (the one i'm entering the Z number onto), see if the sketch number is already present and if it is pull up the next sketch number in the list on the separeate spreadhseet. (I hope this is clear enough)

My Vlookup code is:

=IF(C1070"",VLOOKUP(C1070,'[CrossingsSchedule.xls]Update Worksheet'!$E$1:$AC$10000,20,FALSE)," ")

View 9 Replies View Related

Multiple Answers And Message Box

Jan 12, 2009

I have a message box in my spreadsheet. I want it so if the user puts in multiple answers that certain message box appears, if they put in a different set of multiple answers it shows up. For example, the code below is what I am using now. It says that if the user types 5225 in C16 and 3000 in C17 then a specific msg box appears. I want it to say if the user types in 5225 or 5226 or 5227 the same msg box appears but if they type in 5665 a different msg box appears. How would I change this code? ...

View 9 Replies View Related

Adding Multiple Answers To IF OR Formula?

May 6, 2014

I am using the formula =IF(OR(E2="COLD"),"33%")

Which changes my cell to show the text 33% if the text cold is entered into cell E2. Now what I would like to know, is if I can add multiple catch words to give alternate pre defined percentages. Such as warm and hot to give the respective answers as 66% and 99%

View 3 Replies View Related

Search Criteria With Multiple Answers

Apr 9, 2012

On my spreadsheet i want to find the results from 2 criteria that i entered.

My search criteria are "Oostbos" and "AA8", and excel has to find this from another spreadsheet that i made for rostering.

OostbosN3 evelineAA8N3 evelineAA8N2 MargaAA7

The problem is that i have multiple shifts with the "AA8" criteria, but my function only finds the first one.

I used the following function:

Code:
=IF(C8="";"";INDEX('Afdeling PG PH'!$A$6:$A$28;MATCH(C8&$A$7;'Afdeling PG PH'!$B$6:$B$20&'Afdeling PG PH'!$AI$6:$AI$20;0)))

Also when the AA8 cel is empty that i doesn't show anything.

How the second N3 eveline, shows the 2nd result and so on.

View 1 Replies View Related

Method To Show Multiple Answers

Jan 13, 2009

I am currently maintaining a database that keeps up to date records of employees in my company and their vacations including their nationality dept. etc. for the vacation reports i have a "last day of work," "return to work date," "Actual Return to work date," and at the end a "remarks" column,

Moreover I need to report how many employees per department/Discipline are on leave ex. ( mechanical, electrical, and so on.) That I did using countifs having whoever is remarked as "na" vs. actual return date, Discipline vs. each discipline. All works fine but what i want to ask is there anyway that i can list the names of employees that are on vacation under each discipline?? Ie. if 3 are in the electrical engineering department, can i list their names? or if Today()>Actual Return to Work day (ie they are late and have not arrived yet) is there a way that it can list the names of multiple employees? rather than having to work against each name etc.

View 10 Replies View Related

Lookup Formula To Pull Multiple Answers To A Row

Jul 2, 2014

Instead of trying to explain my challenge, the attached workbook should be self explanatory. My answer is surrounded by the box. I need a formula that would automatically provide this output.

Lookup Scenario.xlsx‎

View 9 Replies View Related

Index / Match And Concatenate On Multiple Answers

Mar 4, 2014

I have 2 worksheets, Worksheet 1 has Customer Magic Number on it as a reference and a few customer details and Worksheet 2 has Customer magic number and contact fields.

I am currently using the formula

=INDEX($C$3:$C$575, SMALL(IF(N2=$D$3:$D$575, ROW($D$3:$D$575)-MIN(ROW($D$3:$D$575))+1, ""), COLUMN(A1)))

to show the contact codes in sheet 1 however I also need to show the Notes which are located in Columns G:I, Is there an easy to use the index & match functions as above with the concatenate function to add the notes in the cell beside where I am inputting the contact codes?

View 2 Replies View Related

Check Multiple Choice Quiz Answers

Mar 28, 2008

I have a table in excel that i need to use to mark a 20 question quiz. I have the correct answers in one column and the students answers in the next column. I want to mark a correct answer with 1 and an incorrect answer with 0 marks. I know how to use IF, however the students answers can vary eg, correct answer could be B and D but the student writes B + D. Is there any way of marking this correct even tho it is not exactly written out correct?

View 6 Replies View Related

Find Multiple Numbers In One Column And Replace With Single Phrase In Another Column

Jan 31, 2013

I have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!

For example from this

invoice #1 / reconciled (DATE)
invoice #2 / reconciled (DATE)

To this:

invoice #1 / paid (DATE) check # (xxxxx)
invoice #2 / paid (DATE) check # (xxxxx)

View 2 Replies View Related

If- Returning 2 Answers

Aug 28, 2008

I have a sheet with this:

cell a1 = numbers from 1 to 10 or nothing
cell b1 = TO or OFF depending on cell a1
cell c1 = numbers from 1 to 10 or nothing

Now I have a formula in cell b1 that detects if theres text in cell a1.
IF A1 = nothing, then b1 = off, else b1 = to.

What I want to achieve is to do 2 things on the IF.

If a1 = nothing, then c1 = nothing and b1 = off. else, b1 = to.

how can I do this?

I looked everywhere and no one speaks of double answers on IF.

View 11 Replies View Related

Different Lists For Different Answers

Dec 3, 2008

Cell C7 is a drop down list with 3 possibles (Consumer,Commercial, Public). Depending on which is chosen, I want Cell C10 to provide a different list of possibles (Consumer Products, Commercial Products, Public Products).

I have tried to create a custom IF statement with the list choice & sometimes it refuses & sometimes it gives me the list, but with the formula string as the drop down options.

Is this forumula possible or is it beyond excel's capability?

View 7 Replies View Related

Consolidate Y Answers To One Row

May 16, 2013

I have a spread sheet that looks like the following. I need to take all of the Y answers and make one line.

Client
ProductA
ProductB
ProductC
ProductD
ProductE

ABC Inc
N
Y
N
Y
N

[Code] ......

If any of the Product columns had a Y in the row, the line would get the Y. If not, an N.

View 5 Replies View Related

IF OR Formulas With 7 Answers

Apr 26, 2006

I am trying to achieve the following in one formula.

IF an amount is greater than 0 and less than 500,000 put in 490
IF an amount is greater than 500,000 and less than 1,000,000 put in 1000
IF an amount is greater than 1,000,000 and less than 4,000,000 put in 2205
IF an amount is greater than 4,000,000 and less than 9,000,000 put in 5000
IF an amount is greater than 9,000,000 and less than 10,000,000 put in 6500
IF an amount is greater than 10,000,000 and less than 15,000,000 put in 7500
IF an amount is greater than 15,000,000 put in 9500

View 3 Replies View Related

Get The Last Word In Phrase

Nov 4, 2006

I'm doing a macro to get the last word in a phrase

View 7 Replies View Related

Formula For Multiple Count - Without Subtotalling For Each Document And Then A Count

Oct 19, 2009

I have a transactional data set with a line for each transaction and I am looking to count the number of documents (each contains multiple transactions) against criteria.....

It looks something like this.....

Column A Column B
Document No Category
11000001 A
11000002 B
11000003 B
11000002 A
11000001 A

Is there anyway to do this without subtotalling for each document and then a count?

View 9 Replies View Related

Macro That Deletes Row Before And After Certain Phrase

Nov 3, 2008

I need a macro that searches my spreadsheet for a keyword in Column B. If it finds the keywords (or an array of keywords would be even better), it then deletes the entire row above the keyword, the row with the keyword, and the row below the keyword.

So for example,

(Column B)
1 - Row Above
2 - Keyword Row
3 - Row Below

If the macro finds the text in column 2, it would delete 1, 2 and 3.

I need to have a second macro that performs a very similar function.
it copies the 3 rows (similar to the example below) to a different sheet based on the keyword.

View 3 Replies View Related

Seperating Parts Of A Phrase Using Vba

Aug 3, 2009

I have a phrase in a listbox formated as such: "Company Name / Company ID # / City, State"

I need to be able to seperate the values out into seperate categories:
Company Name
Company ID #
City
State

Is there any way to do this within the code?

View 5 Replies View Related

Different Answers In Excel Than Calculator?

Jan 18, 2010

I have created Excel spreasheets for our payroll. The answers on my calculator are sometimes different than my spreadsheet. How can I make them tie?

View 9 Replies View Related

Phrase Search Macro

Jan 26, 2014

I would like to write a macro which checks if phrase from column B (lets say 13845) can be found in column A cells.

For example DDH113845G389.

I think that it should be some kind of loop but I'm afraid I can't handle it.

View 6 Replies View Related

Search And Compile Answers

Oct 25, 2007

I have a workbook filled with many tabs / sheets. Each sheet contains the same structure of information and isn't an important aspect of this question.

I've looked at this topic http://www.mrexcel.com/board2/viewtopic.php?t=16683 and I'm unable to follow it well.

Is there a way to search all the tabs for a word and if it matches to copy the entire row into the search tab / sheet? Specifically for each and every searched match.

It would save my users trying to search each tab individually and possibly miss the data they are actually looking for. Hundreds of people use this reference workbook which stores where all our documentation is stored across numerous servers. Each row is one document and its corresponding storage information.

View 9 Replies View Related

Mutilpe Answers To Cell

Dec 12, 2008

How would I write out a formula if I currently have this formula/ =COUNTIF(D6:Q6,"a")*8 on cell S6 but I also want to include/ =COUNTIF(D6:Q6,"a-1")*1 and =COUNTIF(D6:Q6,"a-2")*2 and so on till a-7 and i want all this to read on the same cell.

View 9 Replies View Related

Find Phrase And Look Up Corresponding Cell Value

Mar 24, 2009

I have three columns.
Column 1: A list of items with a very long text description in each cell
Column 2: The Key phrase found within the long description in the cells of Column 1.
(The key phrase is different for each cell).
Column 3: Custom shortened description in text format.
Column 4: Formula to return the Custom shortened description, equivalent to the item in Column 1

How do I create a formula in Column 4 so that Excel searches for the Key phrase found within the string of Column 1 and returns the custom description in Column 4?

View 9 Replies View Related

If Statement With 4 Diffent Answers

Sep 3, 2009

This is my current formula that im using in cell U6

=IF(L6>=500,"Sept 8 2009","Sept 1 2009")

it works fine with just 2 answer cuz if its not one answer is another

=IF(AND($B$2>=0,$B$2>=500),"Sept 8 2009","Sept 1 2009")
found this one online but it got frustrating...i change the values to mine

so what im looking for is a if formula or similar to the following

if L6 is between 0-500 i want U6 to say Sept 1 2009
if L6 is between 501-750 i want U6 to say Sept 8 2009
if L6 is between 751-999 i want U6 to say Sept 15 2009
if L6 is between 1000-1200 i want U6 to say Sept 22 2009

i try both if formulas (stated above) both but both return one or more results...i just want one

so....Example

L6 U6
***(decimal included)
0.00 than i would get the result Sept 1 2009
253.52 than i would get the result Sept 1 2009
647.36 than i would get the result Sept 8 2009
750.01 than i would get the result Sept 15 2009
1100.00 than i would get the result Sept 22 2009

if above 1200 i would get the result sorry your amount owed is to high

formulas are preferred *but will work with macros and other options

*if using macros or vba or what have you please explain how it will work

View 9 Replies View Related

Extract Characters From Phrase

Nov 15, 2006

i have a list of information, and from that i want to extract a certain piece from that information and out it in a new cell...
eg. 'KLM00506', 'KLM00409-10', 'KLM00821-9' etc

as you can see, the info is not in the same length
what i need is to take out the FIRST 6 characters and have the remaining to be in a new cell

anyone has an excel formula that i can use ? not really interested in code, cause in this particular worksheet, everything is at its' place, no code required

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved