counting rows depending on 2 selective values

Hi,

well, I have lots of data and need to do some statistics on it. For
that I need to count the number of rows "H" where a string value "X" is
written but only if these rows also have cell which is NOT empty in
column "E"...

the if stuff drives me crazy (especially as i have a spanish
version...)

any help appreciated :)


-- 
Acid-Sky
------------------------------------------------------------------------
Acid-Sky's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26502
View this thread: http://www.excelforum.com/showthread.php?threadid=401163

0
9/1/2005 3:20:27 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
580 Views

Similar Articles

[PageSpeed] 43

try

=sumproduct(--(h1:h1000="x"),--(not(isblank(e1:e1000))))

"Acid-Sky" wrote:

> 
> Hi,
> 
> well, I have lots of data and need to do some statistics on it. For
> that I need to count the number of rows "H" where a string value "X" is
> written but only if these rows also have cell which is NOT empty in
> column "E"...
> 
> the if stuff drives me crazy (especially as i have a spanish
> version...)
> 
> any help appreciated :)
> 
> 
> -- 
> Acid-Sky
> ------------------------------------------------------------------------
> Acid-Sky's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26502
> View this thread: http://www.excelforum.com/showthread.php?threadid=401163
> 
> 
0
DukeCarey (494)
9/1/2005 4:45:16 PM
Reply:

Similar Artilces:

Script #2
I am using exchange server for a company based calendar, and POP3 email. Exchange keeps setting itself as the default account, and when my user try to send email, the emails will return to them saying unable to send mail. There is supposed to be script that you can disable to stop this from happening. Does any one know how to do this? On Wed, 5 Oct 2005 06:01:04 -0700, "Rkd2398" <Rkd2398@discussions.microsoft.com> wrote: >I am using exchange server for a company based calendar, and POP3 email. >Exchange keeps setting itself as the default account, and when my user ...

A CString question #2
Hi to all, Given the following: CString sTheString = "Hello world"; void UseTheString(CString s) { ... } Where UseTheString() is a function that takes a string as an argument. My questions are the following: 1. Is it rather better to use the below version of the function? If yes, why? void UseTheString(LPCTSTR s) { ... } 2. When can I substitute a CString with a LPCTSTR and vice versa? Thanks a lot, Geo Geo wrote: > Hi to all, > > Given the following: > > CString sTheString = "Hello world"; > void UseTheString(CString s) &...

Junk E-mail filter problems in Outlook 2003 #2
The Junk E-mail Options - Safe Senders tab states "E-mail from addresses or domain names on your Safe Senders List will never be treated as junk e-mail. In that list I have (for example) nytdirect@nytimes.com which is the from address for a daily DealBook summary to which I have subscribed. Yet, in an unpredictable pattern, email from this address lands in my Junk E-mail folder. Each time, I click on the "Not Junk" toolbar button and am then informed in a "Mark as Not Junk" dialog box that "This message will be moved back into the Inbox Folder." I then leave...

Sumproduct and the darn dates! #2
Great - it worked! Thank you. : -- Krelle ----------------------------------------------------------------------- Kreller's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1163 View this thread: http://www.excelforum.com/showthread.php?threadid=27044 glad it helped -- Don Guillett SalesAid Software donaldb@281.com "Kreller" <Kreller.1edlop@excelforum-nospam.com> wrote in message news:Kreller.1edlop@excelforum-nospam.com... > > Great - it worked! Thank you. :) > > > -- > Kreller > -----------------------------------------...

CRM 1.2 & 3 on the sae server
Is it possible to install a CRM 3 on the same server (and not to upgrade) where the 1.2 CRM is already installed? I dont believe you can do that, unless you are using server virtualization. "David Massard" wrote: > Is it possible to install a CRM 3 on the same server (and not to upgrade) > where the 1.2 CRM is already installed? Both versions use the same registry hive, so they can not run on the same machine. -- Matt Parks MVP - Microsoft CRM "chad.buser@ncsi.cc" <chadbuserncsicc@discussions.microsoft.com> wrote in message news:885DC6E8-3226-4D1F-9...

Data analysis by 2-dimensional data table + database
Hello, in an old Excel-manual (possibly way back to version 3) by Microsoft there was an excellent example of a 2-dimensional datatable (in German: Mehrfachoperation) with input variables based on database results. Now I would need to apply this technique, but my old manual is lost, and I can't figure out by heart exactly how this was done. Does somebody still have an old manual from which to draw the model, or can let me know the deatils of the trick? Thank you in advance. Kind regards, H.G. Lamy H., I think that you are talking about a cross-tab table....

Export chart value
I would like to export a chart without the link source from table data worksheet. Please help me. Thanks:mad: -- excelvn ------------------------------------------------------------------------ excelvn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32724 View this thread: http://www.excelforum.com/showthread.php?threadid=555867 Hi, Maybe Jon Peltier's page on delinking chart data will help. http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html Cheers Andy excelvn wrote: > I would like to export a chart without the link source from table da...

group & summarize diff rows of data that have something in common
I am trying to arrange a large amount of data (inventory) in 2 different ways. 1) Group all identical parts into one row and 2) Group all identical locations into one row. I dont know how to do this. I am dealing with over 3,000 rows. Any suggestions? Depending on your workbook layout, you may be able to use a pivot table to summarize the data. There are instructions in Excel's Help, and Jon Peltier has information and links: http://peltiertech.com/Excel/Pivots/pivotstart.htm pkunAAC wrote: > I am trying to arrange a large amount of data (inventory) in 2 different &g...

use the current time to return a value
Simple formula? if the time is >=07:00:00<15:00:00, G$1. = 1 if time >=15:00:00 < 23:00:00 G$1 = 2 if Time >=23:00:00 < 07:00:00 G$1 = 3 I have tried to do this a few different ways and have been unsuccessful. Could someone help please? One way: G1: =IF(OR(MOD(NOW(),1)<7/24,MOD(NOW(),1)>=23/24), 3, IF(MOD(NOW(),1)>=15/24, 2, 1)) In article <1133191901.819183.53950@g14g2000cwa.googlegroups.com>, sjd371@netzero.com wrote: > Simple formula? if the time is >=07:00:00<15:00:00, G$1. = 1 if time > >=15:00:00 < 23:00:00 G$1 = 2 if Time...

Web Integration #2
Is anyone using a web integration solution with more than one site from the same database other than the Kosmos solution? Bill Hobby Central On Oct 26, 12:25=A0pm, Hobby Central <HobbyCent...@discussions.microsoft.com> wrote: > Is anyone using a web integration solution with more than one site from t= he > same database other than the Kosmos solution? > > Bill > Hobby Central We have 7 or 8 plus e-commerce sites using one database on the Nitrosell platform. Nitrosell ties in real time with your inventory, and as you add additional web stores you add additional columns...

Sorting problem #2
Dear all, I have a list of integers from A1 to A10. If I would like to store the absolute address of the third cell counted from the top in column A containing "1" in B1, what formula should I use? For example, if the data from A1 to A10 are 3, 1, 1, 2, 1, 2, 2, 2, 1, 3, I want B1 stores "$A$5" as A5 is the third cell containing "1". Thanks in advance. Best Regards, Chris Chris, One way Add a column B with the cell names i.e a1,a2,a3,a4,a5,etc Enter in C1 ...........=B3 do a sort on A column after selecting a1:b10 Use the undo button, enter ne...

Followup Flag Reminder #2
Hello, I have issues using 'Flag Reminder' feature. I have setup rules wizard to move messages to different folders. Now if I flag a message that has been moved to a different folder, I don't get any reminders, though I am getting reminders if the flagged message is in the Inbox itself. Has anyone faced this problem? How do I resolve this? Thanks, Ravi Reminders will only fire from the Inbox, Calendar, Contacts, and Tasks folders in the default mail store. You'd need a 3rd party add-in to make them work in other folders. See www.slovaktech.com for a couple of add-ins...

Hide row and column labels
How can I hide the row and column headings in a spreadsheet. (the a, b, c, etc. across and the 1, 2, 3, etc. down) Thanks! Hi Pam! Use: Tools > Options > View Remove check from "Row and Column Headers" OK -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au How about programmatically? On Fri, 25 Jun 2004 06:20:20 +1000, "Norman Harker" <njharker@optusnet.com.au> wrote: >Hi Pam! > >Use: > >Tools > Options > View >Remove check from "Row and Column Headers" >OK Record a macro while you...

Only 2000 characters in CRM 1.2?
HI, I have using MS CRM 1.2 and we seem to be limited in the notes section for Phone calls and letters to 2000 characters which we find VERY limiting. Is there any way around this at all? CHeers, Paul Yeah...upgrade! EnigmaPaul@gmail.com schreef: > HI, > > I have using MS CRM 1.2 and we seem to be limited in the notes section > for Phone calls and letters to 2000 characters which we find VERY > limiting. > > Is there any way around this at all? > > CHeers, > > Paul ...

How can I get Positive values only from the random number generat.
I am generating a random group of numbers using Excel's add-in "Random Number Generator". I am using the normal distribution, but I want positive values only. I know that the test I am doing will work with the negative values I have generated, because the mean/stdev are correct. However, the activity for which the numbers are associated can never be negative, so my presentation will be less effective. Any ideas? You could use the randbetween function and specify a positive range i.e. =Randbetween(1,10000000) HTH Jimbola "Markw3700" wrote: > I am gene...

Excel book #2
Witch is the best excel book? marquetta.kayes@booomail.com Depends upon where you start from, and what you want to achieve. Check out John Walkenbach's Excel 2003 Bible as a start. -- HTH RP (remove nothere from the email address if mailing direct) "marquetta kayes" <marquetta.kayes@livepharm.com> wrote in message news:1a5c7$42e4a746$d99c5301$8690@allthenewsgroups.com... > Witch is the best excel book? > > marquetta.kayes@booomail.com Considering you can get it delivered to your inbox, free, you might want to try MrExcel's new book. http://www...

Cash Sales Entry #2
How to do a cash sales entry? You mean a cash-basis accounting type of entry or a sales transaction where you are receiving cash (as opposed to check or credit card)? New User wrote: > How to do a cash sales entry? ...

select all for check box..
HI.. I have a check box field in a continuous form... name is mCheck I want to have a command button Select All such that when clicked, all check boxes in the continuous form would be selected...Thank you.. -- ai® Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200802/1 A continuous form would display a set of records. Are you saying that you want all the records in the set marked with a check-mark? If so, one way to do this would be to run a query via code behind the command button that updates the field underlying the checkbox in a...

MS POS 2.0 install in Spanish
Hi Everyone, I want to install MS POS 2.0 in Spanish. Has anyone accomplished this? Thanks, Patricia Hi Patricia.... You have to contact Mr Louis Piedra in Scansource technologies. They are the latin american partners of Microsoft RMS. I understand that they have already the files needed to run RMS 2.0 in spanish. "Patricia" wrote: > Hi Everyone, > > I want to install MS POS 2.0 in Spanish. Has anyone accomplished this? > Thanks, > > Patricia ...

Cannot delete a public folder #2
Hello, I have a public folder under all public folders that I cannot delete. When I try to delete it in Outlook is says "Some items could not be deleted. They were either moved or already deleted, or access was denied." When I try to delete the folder from ESM it asks me if if I'm sure, I click yes and then get prompted for authentication. I have tried both the domain adminstrators account and the account I use to administer the domain and Exchange. It just keeps prompting me for a user name and password until finally it says access denied. I've gone through just abou...

customise timeline
Hi there, I need to set up a customised timeline with a "day 0" - it's to do a month end timetable for an accounting function. Day 0 would be the last day of the month and is the reference point day -3, -2, -1, 0, 1, 2, 3 4 etc Do you know how I can set this up? At the moment it goes from day 1 to day -1 and I need a day 0 in between. Thanks Thanks, Hi robbo, Are you trying to customize the Gantt chart timeline? If so, I'm sorry, the answer is no. I hope this helps. Let us know how you get along. Julie Project MVP Visit http://project.mv...

Unable to open excel file #2
Hi. I have problem openning an excel file from windows explorer or from my desktop. However, when I open the excel program and open a file manually(File-Open----) everything works fine. I get an error message that the file could not be located. I noticed that it adds .xls to every word separated by space. For example: My file is in C:/Shared/Folder/test.xls, it tries to open: C:/Shared.xls then Folder.xls then test.xls Please help. Thanks. --- Message posted from http://www.ExcelForum.com/ Hi! I think the steps below will solve this problem: 1. Exit Excel 2. From the Startmenu, cli...

Deleting Rows 04-20-10
Hi Folks, I want to delete two rows - one row has the words "Employee Name" in the first column, and the row before it. This for-next loop removes the row with "Employee Name": For Lrow = LastRow To FirstRow Step -1 If .Cells(Lrow, "A").Value = "Employee Name" Then _ .Rows(Lrow).Delete Next Lrow Any suggestions how to delete the row with "Employee Name" and the one before it? Thanks! Scott Here is one way, without a lot of re-write. For Lrow = LastRow To FirstRow S...

hide colum based on cell value on each worksheets
I have a workbook with several worksheets. I am trying to write a VBA macro that would look at row 5 to see if any cells indicate "Hide column". If it does, it would hide all the columns indicating "Hide column" on this worksheet. Then it would continue the same process to the next worksheet, and the next one ... I currently have the macro working for the individual worksheet (see below), but I would like to have it work to do all the worksheets of the workbook. Sub HideColumn() Application.ScreenUpdating = False Application.Calculation = xlCalculation...

column/block/rectangular selection of text in Word 2008
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel What happened to the ability to select a column/block/rectangular selection of text by holding down the alt/option key in Word 2008? How do I make a vertical text selection? <br> Thanks in advance. <br> JBM "Nothing" and "The Same Way" :-) However various things may get in the way: Spaces is one, having the Toolbox open is another. Anything that distracts the focus from the document page can potentially interfere with the operation of this function. As usual, the ...