#### Countif in two columns and in different cells

```I'm not sure which function I need to add text "p"   and "cps"   when this
data is in different columns.   The total in this ex: would be p+cps = 2.
Any help would be greatly appreciated. I'm new at this.

shift      class
p           cps
p            x
p            x
p            x
p            cps

total p+cps = 2
```
 0
ferde (10)
10/7/2005 6:20:09 PM
excel.misc 78881 articles. 5 followers.

2 Replies
410 Views

Similar Articles

[PageSpeed] 1

```
"ferde" wrote:

> I'm not sure which function I need to add text "p"   and "cps"   when this
> data is in different columns.   The total in this ex: would be p+cps = 2.
> Any help would be greatly appreciated. I'm new at this.
>
> shift      class
> p           cps
> p            x
> p            x
> p            x
> p            cps
>
> total p+cps = 2

Assuming that shift is in column A and class is in column b

=IF(AND(A2="p",B2="cps"),COUNTA(A2:B2),"")

Regards
Peter
```
 0
10/7/2005 7:07:04 PM
```Try this:

=SUMPRODUCT((A2:A100="P")*(B2:B100="cps"))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"ferde" <ferde@discussions.microsoft.com> wrote in message
news:5F21E410-0F9E-4F40-9ECD-91D5F5B9A25F@microsoft.com...
> I'm not sure which function I need to add text "p"   and "cps"   when this
> data is in different columns.   The total in this ex: would be p+cps = 2.
> Any help would be greatly appreciated. I'm new at this.
>
> shift      class
> p           cps
> p            x
> p            x
> p            x
> p            cps
>
> total p+cps = 2

```
 0
ragdyer1 (4060)
10/7/2005 7:31:39 PM

Similar Artilces:

adding values if the criteria in two columns are met
I need a formula to add values with two criteria in a column. I am trying to add the values if two criteria are met. For example below, I want to add the total of the amount that are for 2008 and has a Status of No. The answer would be \$86,750. I could use the Pivot Table, but It's not what I want to use. Is there another way to do it with a formula? Amount Percent Year Status 75,000 3.00% 2008 No 6,750 3.00% 2008 No 9,100.00 2.00% 2009 Yes 620,000 2.00% 2009 No 2,800 2.00% 2009 No thank you, mayasmom Try this: =3DSUMPRODUCT(--(C2:C10=3...

Help needed on date matching and cell reference.
Hi, I need a formula to return the value of a cell from a nearby column, based on a matching of the months and years within a range of dates to the months and years within a given date. Here is the setup: Cell BA58 contains the given date. Column AP, starting in Cell AP59 and going down to AP2000 contains the array of dates that need to be evaluated to find which date matches the given date in Cell BA58 Column AR, starting in Cell AR59 and going down to AR2000 contains the array of numbers from which the result must be displayed. If a match is found between the given date in...

create chart from series grouped by value in another column
How to create a chart from series grouped by a value from another column? Example The sheet has these rows: A, DATE, 1 A, DATE, 6 A, DATE, 7 B, DATE, 3 B, DATE, 15 B, DATE, 6 B, DATE, 6 A{1,6,7} is a series and B{3,15,6,6} is a different series. I have around 2000 different series like that in my excel. How can I auto-create charts for each series (i.e. not selecting each series one by one)? Creat another Sheet with the data series in the order you wish to graph them. "HarCo" wrote: > How to create a chart from series grouped by a value from another column? > Example > ...

How do I merge similar data across different excel worksheets?
If you have a unique key value that's on each sheet, you could use =vlookup() or =index(match()). You could look at Debra Dalgleish's site: http://www.contextures.com/xlFunctions02.html and http://www.contextures.com/xlFunctions03.html MK wrote: -- Dave Peterson ...

Relationship with multiple columns?
Hello, This is my current situation. I have a staff roster (table) with employee information including 4 columns of work logins. Some will only have one login but some have multiple logins and I need to be able to pull information from the staff table into a query/report with data from a table that only contains a login for identification. I cannot see how to make a relationship that looks at multiple columns for a match to the same "key". Hopefully this is something simple someone can help with. Dustin "Simple", yes... but not if your data is organized like a spreadshee...

Combining two multiple-page publications
How do I combine two multiple-page publications in Publisher? TMP wrote: > How do I combine two multiple-page publications in Publisher? Do you have Publisher 2007? -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org I am jumping into this post b/c I have the same question - I am using Publisher 2003. I have 5 documents, each about 20-40 pages in lenght that I would like to combine into one document. Any ideas?? "Ed Bennett" wrote: > TMP wrote: > > How do I combine two multiple-page publications in Publisher? > > Do you have Publisher 2007? > ...

In cell drop down button disappeared
I have a column with a data validation list. My drop down arrow doesn't show up anymore when I select the cell. It has been working for a year with no problem. Where did it go? I looked in Tools-Options to see if anything was unchecked, but all looks good. Any ideas? Joe Excel 2003 If you select the cell and do data>validation is allow list and in-cell dropdown still there (and checked)? -- Regards, Peo Sjoblom "lunker55" <this_is_not_my_email_address@hotmail.com> wrote in message news:e5LtKqoGFHA.2280@TK2MSFTNGP15.phx.gbl... > I have a column with a data va...

Referencing a referenced cell
Sheet1!C4 contains the formula =Sheet2!A1 I want Sheet1!D4 to find out what cell Sheet1!C4 is referencing an then return the value two columns over. (Offset will take care o returning the value two columns over). My problem is I need to kno which cell Sheet1!C\$ is referencing. If I use the formul "=OFFSET(Sheet1!C4,0,1)" I get the value from one column to the righ of Sheet1!C4, I need the value of one column to the right of the cel being referenced by Sheet1!C4, (Sheet2!A1) How would I do this. -- Message posted from http://www.ExcelForum.com Hi see your post in Excel.mis...

I've created a 'fillable form' and somehow (magically) when the tab key is pressed in certain cells, the cursor jumps to the next appropriate cell to be filled (Example I filled in cell B1 and the next fillable cell is D4. If I hit the tab key after typing in B1, the cursor will jump to D4). But this does not happen in all cells or at the appropriate places. I don't know how I managed to get it to do what it does already. Is there is a way to fine-tune it? When you protect a sheet, tabbing will cause the active cell to jump between the unlocked (aka unprotect...

Copy from many cells and paste to one
I need to copy a range of cells (C7:C20) and past the contents into one cell (B4). the concatenate function is not the solution because I need the results to appear as separate lines in the cell (B4) when pasted. (leaving a "return" between lines) Weird I know but Ahem, "It's for a friend". ;) Thanks, Robert -- eoreality ------------------------------------------------------------------------ eoreality's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24549 View this thread: http://www.excelforum.com/showthread.php?threadid=381448 ...

Different Views
I'm a little confused. How come I get different views depending on where I use CRM. If I use CRM from the server, I can get access to pretty much everything, settings etc, but when I use my desktop and goto http://server:5555 I only get My Workspace and a few other options, no settings. Also if i personalize my workspace, there are more options if I use the server than I do if I use my desktop? M This sounds like your user account doesn't have the roles assigned to it to give you access from the desktop. Each user can have unique access rights depending upon how you have set ...

What are the differences between ActiveX, OLE and COM
....thx.. COM is *component* object model, not common. >-----Original Message----- >Amuro wrote: >> ...thx.. > >There's no difference. Just marketing names > >COM => Common Object Model (Microsoft word for CORBA) >OLE => Object Linked embedding (new word for COM, used mainly in >MS-Applications) >ActiveX again new word for OLE > >Let the flaming begin ;-) > >Greetings, Urs > >. > Amuro wrote: > ...thx.. There's no difference. Just marketing names COM => Common Object Model (Microsoft word for CORBA) OLE => ...

Merge two cells together without losing value of one cell
Is there any way to merge two cells together without getting one value deleted? As an example, I'd like to add ";" after a number, but when I do a merge the ";" would get deleted. Thanks. I have 300 cells with numbers to add ";" to. "Sharon" wrote: > Is there any way to merge two cells together without getting one value > deleted? As an example, I'd like to add ";" after a number, but when I do a > merge the ";" would get deleted. Thanks. On Aug 7, 10:00=A0am, Sharon <Sha...@discussions.microsoft.com&g...

Copy formulas in Column
Hi, I have to copy numbers from cell a1, b1,c1...........z1 to a20,a21,a22,a23... so. for e.g-- a1 = 21 b1 = 23 c1 = 24 etc z1 = 40 i need to copy as follows:- a20 = a1 a21 = b1 a22 = c1 Is there an easy way to copy the numbers instead of typing one by one .... I have many records to copy..... Thanks for your kind help :) Meeru --Select the range A1:Z1 and copy --Select cell A20. Right click>PasteSpecial>check 'Transpose' and click OK -- Jacob "Meeru" wrote: > Hi, > > I have to copy numbers from cell a1, b1,c1...........

How do I delete cells in Excel without changing the data in others
I want to get rid of some of the rows in my spreadsheet but the data in the ones I am leaving behind are linked by formulas? As soon as I delete them all the data goes from the the others. Gemma, I may not understand your situation. An example of what I think you're saying is: Cell A2: A1+1. You want to delete cell A1, but leave A2 with the value it currently has. If this is the case, you need to copy A2 and the, using paste special, paste the value back into A2. This way A2 no longer has any formula at all and will remain unchanged when you delete A1. Art "G...

I need to transpose an Excel worksheet where every fourth cell is selected and placed into a single column of another workbook. Can I write a formula to do this so I don't have to manually copy each cell I need? If your original data were in col. A, you could put this in A1 of a new wb: =OFFSET([Book8]Sheet1!\$A\$1,ROW()*4-4,) and drag down. It'll pull in the values in row 1, row 5, row 9, and so on. HTH Jason Atlanta, GA >-----Original Message----- >I need to transpose an Excel worksheet where every fourth cell is selected >and placed into a single column of anoth...

Excel cell format #2
how can i display preceding zeros in excel without formatting as text? In article <5CBDC357-B0B2-49C2-906C-73E94C6172B9@microsoft.com>, "rockfam8" <rockfam8@discussions.microsoft.com> wrote: > how can i display preceding zeros in excel without formatting as text? Precede your entry with an apostrophe. For example... '012345 Hope this helps! Or give it a custom format like 00000 (as many 0's as you need) rockfam8 wrote: > > how can i display preceding zeros in excel without formatting as text? -- Dave Peterson ...

Text to columns issue
Some how I got set up so that when I paste text into an excel sheet it auto does the text to columns funtion. Each word is in its own cell. I would very much like to know how I got set up in this way and even more how I can stop it. I have opened new sheet and books and the same thing still happens so I think it must be some sort of global setting but I just can't find it. Thanks in advance. -- sminor ------------------------------------------------------------------------ sminor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37606 View this thread: http...

CRM Time Clock Different Then System and Server Clock
Hi All, I have run into an odd situation whereas one of my users CRM lists a different time clock within CRM. When they create an entry in CRM there is a three hour difference between the time they enter it and what is actually recorded within CRM. For instance, the user will enter an update of information about a client and a conversation they had. They enter it at 11am and yet CRM shows that the entry was entered at 8am. Both the users local time clock and server are set for the correct time for Eastern Standard Time. This only appears to have happened with one user. No other ...

vlookup function to return the cell address of the found item
I have a one column list of data (around 3,000 items) - and I am using the vlookup function to determine if an item is in that list using something like the formula below: =if(iserror(vlookup(A1,D1:D3000,1,false)),"not in list","in list") I would like to know if I can have this function return the cell address or row number to indicate the location of the item in the list - is this possible? Thank you for your time and assistance You can return the (relative) row number using MATCH, like this: =if(iserror(vlookup(A1,D1:D3000,1,false)),"not in list",MATCH(A1,...

Insert file name into Cell
Is there a way to insert the file name into a cell, rather than on header/footer? Hi Bonny, 1996FEDT.XLS =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1) D:\driveM\excel\TAXES\1996FEDT.XLS [Sheet1] =SUBSTITUTE(SUBSTITUTE(CELL("filename",A1),"[",""),"]"," [") & "]" for more information, worksheet examples, and coding examples for pathname, filename, sheetname and combinations of ...

Date when I last change cell in a row?
Please, Help. In my database, every row represents a separate client. I wonder if it is possible to input the date when I last changed any value in a particular row. This way I will know when exactly I last updated the information for client in row 4, 5 etc. I don't need the date when the document was last saved. Please, let me know if it can be done in Excel. Thank you, Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Row = 5 Then Me.Range("H1").Value = Date ...

Displaying cell references next to embedded cells in Word 2000
Hello Group. I've embedded some excel cells into my report written in Word 2000. Is there a way I can display the cell references next to the embedded object in the printout. For example if cells B10:C15 are the embedded cells, I want those who read the printed document to see exactly which rows and which columns the numbers belong to. Something like this: B C 10 Jan 10% 11 Feb 15% 12 Mar 34% 13 Apr 14% 14 May 10% 15 Jun 12% So when I say somthing like "The formula used here is C10/SUM(C10:C15)" my readers would be able to refer to the ...

Adding a changing number of cells.
I am trying to add up part of a row of numbers. The number of cells in the row that I am trying to add will change depending on a variable in another cell. For instance, if there are numbers in the range a1:t1, I am trying to obtain the sum of the range that will always start with cell a1 and end with ?1 where "?" corresponds to a number in cell b1. The number in b1 is a variable and will change occasionaly based on other conditions. So if the number in cell b1 is "6" I would want the sum of a1:f1. Does anyone have a suggestion? Thanks, Thanks One way: =SUM(...

Paste a range of cells in the body of an email
I have been trying to get the code to work for copying a range of cells and pasting it into the body of an email I am using excel 2007 and Lotus Notes 7.0.2 My current code works kinda sorta. It will copy and paste the text into the body of an email. But it does not open a new email it replys to a email that is in my in box. It also sends an email but it is a blank email. I would do just an attachment but the department I need to send the email to says they can not open the attachment........... Anyone have code that can do this? thanks Marie All you ever need to know ...