=COUNTA() with multiple look-up

Hi All,

I would like to count the number of items that have a value "AAA" in
column A and an amount >0 in column B.

As I am not quite sure how to incorporate two look-ups in a counta
formula I was wondering if anyone can give me the formula?

Many thanks!

Rgds,
Robert

0
4/5/2007 12:58:00 PM
excel 39879 articles. 2 followers. Follow

5 Replies
1005 Views

Similar Articles

[PageSpeed] 36

Try something like this:

=SUMPRODUCT((A1:A100="AAA")*(B1:B100>0))

or....if there may be some text cells in B1:B100 interspersed with the numbers
=SUMPRODUCT((A1:A100="AAA")*ISNUMBER(B1:B100)*(B1:B100>0))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Robert" wrote:

> Hi All,
> 
> I would like to count the number of items that have a value "AAA" in
> column A and an amount >0 in column B.
> 
> As I am not quite sure how to incorporate two look-ups in a counta
> formula I was wondering if anyone can give me the formula?
> 
> Many thanks!
> 
> Rgds,
> Robert
> 
> 
0
RonCoderre (135)
4/5/2007 1:08:06 PM
Hi Ron,

Thanks for the suggestion!

When I fill out the formula I get #NUM! as a result.

I adjusted the formula as follows: =SUMPRODUCT((Flash_data!
A:A="PHILIPS FFC")*(Flash_data!D:D>0))

Am I forgetting something?

Thanks again

Rgds,
Robert

0
4/5/2007 1:27:22 PM
SUMPRODUCT does not work with entire column (D:D)
Try something like D1:D6500
best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Robert" <p.strijbosch@chello.nl> wrote in message 
news:1175779642.266675.31290@n59g2000hsh.googlegroups.com...
> Hi Ron,
>
> Thanks for the suggestion!
>
> When I fill out the formula I get #NUM! as a result.
>
> I adjusted the formula as follows: =SUMPRODUCT((Flash_data!
> A:A="PHILIPS FFC")*(Flash_data!D:D>0))
>
> Am I forgetting something?
>
> Thanks again
>
> Rgds,
> Robert
> 


0
bliengme5824 (3040)
4/5/2007 1:39:37 PM
SUMPRODUCT cannot reference entire columns; it must refer to ranges.

Try this:
=SUMPRODUCT((Flash_data!A1:A65535="PHILIPS FFC")*(Flash_data!D1:D65535>0))

NOTE: the formula refers to the next-to-the-last row....NOT the last row 
(65536)

Alternatively, you could skip the first row, instead:
=SUMPRODUCT((Flash_data!A2:A65536="PHILIPS FFC")*(Flash_data!D2:D65536>0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Robert" wrote:

> Hi Ron,
> 
> Thanks for the suggestion!
> 
> When I fill out the formula I get #NUM! as a result.
> 
> I adjusted the formula as follows: =SUMPRODUCT((Flash_data!
> A:A="PHILIPS FFC")*(Flash_data!D:D>0))
> 
> Am I forgetting something?
> 
> Thanks again
> 
> Rgds,
> Robert
> 
> 
0
RonCoderre (135)
4/5/2007 1:46:02 PM
Hi Ron and Bernard,

Thanks, that does the trick!

Rgds,
Robert

0
4/10/2007 7:12:40 AM
Reply:

Similar Artilces:

Multiple Users #2
I have three workstations that several users share. The workstation is usaully logged on in the morning and different users use it through out the day. All of the users have email accounts. Is it possible to have Outlook prompt for user name, password upon opening. This would allow for easy of use and security for all users. Thanks. If not possible is there another method with out having to asign a logon to each user and having them logout and back in. Do you use an Exchange Server? -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you checked...

Multiple Timers with OnIdle
I need two different timeouts, controlling the idle time. The first should fire once every minute, the second only after 20 minutes. That's what I had planned to do (but won't work): In OnIdle() I call SetTimer() twice with different IDs and timeout values. But: As soon as the earlier (1-minute) timer fires, it sends a WM_TIMER message which in turn triggers another OnIdle() counter restart which in turn will result in 2 fresh calls of SetTimer(). So, I'll never get to the 20 minutes. Because I need to know the idle time, I can't control the longer timer from the short...

How do I choose multiple values in a drop down box?
I'm hoping to create drop down boxes in a spreadsheet that can have multiple values. I haven't been able to make this work and am wondering if it's even possible. Any help would be great, thanks! You can use Data Validation to create dropdown lists. There are instructions in Excel's help, and here: http://www.contextures.com/xlDataVal01.html Kristy wrote: > I'm hoping to create drop down boxes in a spreadsheet that can have multiple > values. I haven't been able to make this work and am wondering if it's even > possible. > > Any hel...

Multiple emails for one contact in distribution list
I have created a group to send out bi-monthly emails from my personal email account. One of the people in the group would like to have the email sent to her work email and to her personal email. I have attempted to add both email addresses to the group, and initially it does. After syncing, however, the personal email disappears from the group list because it is not the primary email listed in the contact. Is there a way to include both email addresses without creating a separate contact for just the personal email? I am running Outlook 2010 beta. My email is through Hotmai...

Multiple Chart Report
I wish to have a multiple chart report displaying 1 chart per Employee displaying the Categories he has worked his hours in. I have a query that when Run gives me the Data I expect from which I build a Report with the Chart in the Detail Section. I have an EmployeeID group which has the EmployeeID number in. What I get is a Chart I want with all his hours displayed as required, but then followed by the same chart a number of times depending on how many Categories he has worked on e.g if he has 6 Categories, I get 6 charts the same, if he has done 2 Categories, I get 2 charts ans so on for al...

Pivot Table/Multiple Workbooks
Is there anyway to create a pivot table using information from multiple workbooks (not worksheets?) I have 3 files: count1.xls count2.xls count3.xls All files have same headers but each file represents a different month. How can I create a pivot table using these 3 excel files? I also want the pivot table to get updated everytime I make a change to one of the excel files. If this is doable, what is maximum number of files that I can link to that pivot table? Thanks. You can use a pivot table across mulitple workbooks by using the "Multiple Consolidation Ranges" in the ...

Multiple PM Check Formats?
Our company has GP 10.0 hosted on Citrix. This way multiple GP companies can access the system. So far the only hitch in the modified reports is the PM checks. The other reports I've standardized across companies. But the PM checks are based on pre-ordered check forms. Two different companies are looking to use the PM Computer Check with Stub on Top and Bottom - Graphical. The layout between them differs slightly. I can try duplicating the custom Report Writer entry to split them off, but when I do I don't see the copy listed in the Payables -> Setup window for the ...

Sorting numbers and doing a counta
I have a spread sheet that has repeating numbers, i.e. techtip #'s, i multiple columns. I need to sort these numbers and do a counta on them. Example below: Column A Column B Row 1 1212 4545 Row 2 4545 1212 Row 3 1212 4545 Ideally, I need to have, at the bottom of each column, the sorte techtip #'s and how many are there of each techtip # (counta). Because of the number of techtip #'s, manually sorting and counting i out of the question. Thanks in advance, Jef -- tmi!tmi ------------------------------------------------------------...

Multiple Storage per user
Dear All, We have an Exchange Enterprise Server 2003 with two disks: one small and fast, and another slow and big. We would like to set up our users with a way of archiving their old (but not to be deleted) e-mails into the slow and big disk while keeping their Inbox and other folders in the small and fast disk. We thought that auto-archiving would be a way of doing it, but it turns out that auto-archiving archives to a .pst file, and we have had serious problems with the stability of those in the past, so I'd rather not use them. Is there a way to: 1. separate a user's mailbo...

Unhide Multiple Sheets at once
Excel 2002 SP3 Win XP HE Hi, I know how to hide multiple worksheets at once, but cannot seem to find a way to unhide *multiple* sheets at once. Anyone? stef Only through code. Sub unhide() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11...

hiding multiple lines in excel
I am using custom views from a master document to filter information. I would like to hide evry 2nd & 3rd row. Because the document has approx 2000+ rows, is there a way to do so without recording a macro while I manually hide every 2nd & 3rd row? Be kind, I am a rookie Sub hiderows() For i = Cells(rows.Count, "a").End(xlUp).row To 2 Step -3 rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Khardy3352" <Khardy3352@discussions.microsoft.com> wrote in message news:B88AD055-92E9-4DF2-9BF1-...

multiple emails coming and going?
I recently set up a new computer. Use XP pro and Outlook 2007. Recently email recipiants say that my emails have replicated up to seven times. Today, I received for the first time an email from someone and I got four copies of the same email. What can I do to correct this madness? This is irratating to those who receive my memos not to speak of personal frustrations. Can anyone help here. Am I going to have to reinstall Outlook? Thanks for any assistance. Jerry My apology ... the duplication is only one way... just the emails I send out Jerry "Jerry" <jerry@home.home>...

XP Look in CDHtmlDialog
I have created an application, and I have enabled Common controls V6 by creating a manifest file and Initiating Common Controls. It works properly whenever I use a standard Dialog but when I use a CDHtmlDialog and create a html button or a html radio button, it has the old Pre-Windows XP look, Is there a way to enable XP Look in the buttons created with HTML ? overwrite OnGetHostInfo DOCHOSTUIFLAG_THEME Internet Explorer 6 or later. Specifies that the hosted browser should use themes for pages it displays. DOCHOSTUIFLAG_NOTHEME Internet Explorer 6 or later. Specifies that the hosted ...

macro to read and sort data from multiple text files
Hello there !! I have x number of ascii text files that have space delimited data columns (files may have 3 to 40 data columns). There are about 25 header rows at the top of each file but I am not going to use info from these rows. I also have a master.xls file. Could someone please help me create a macro to do the following - A) read first *.txt file from the given folder into sheet1 of the master.xls file. NOTE: I have already set up the sheet2 of the master.xls to sort required rows of data from sheet1 (based on row headers) using vlookup to populate a 40 column wide array. B) Copy the ...

couting occurence using multiple dates in single cell
Hi, I need to count the no. of meeting that took place in each month. My worksheet is January Feb to Dec column A column B Column C to Column M Column C (data) 1 name no of meeting No of meeting 2 Jack 1/1/09, 1/3/09, 2/1/09, 2/5/09, 3/1/09.4/1/09 3 Peter 2/1/09, 3/1/09,3/15/09 4 Paul 3/1/09, 4/1/09, 4/2/09 is there a way to count the ...

multiple labels
How can I print labels when the data base indicates a number of labels. In other words, it I need 2 address labels for one person and then 6 address labels for the next person. In my data base I have a column that states the number of labels needed for each address. Does this make sense? Please advise. Thankyou Try the KB article: http://support.microsoft.com/kb/179288/en-us -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Penny T" <Penny T@discussions.microsoft.com> wrote in message news:5432D36B-FF63-41B6-A42...

How can I solve for multiple unknown variables in Excel?
I'm trying to use Excel to solve for multiple unknown variables. I've been searching and have not found how to go about this. Does anyone know how to go about this? I'm sure that most of us do. Care to share exactly what you're looking to accomplish? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Marion" <Marion@discussions.microsoft.com> wrote in message news:D...

can add multiple analysis code in a single FRx Row format
Currently we can add several analysis code in FRx Row format by using several rows (each row will contain only 1 analysis code - limitation of FRx with MDA). The client has many analysis codes in each analysis group. What they need is to add some of the analysis code in a single row; separating it into several rows will add more work in designing the FRx report. There should be a way for next releases of FRx to handle adding mulitple analysis codes in a single row or column of the report. ...

IF COUNTIF & COUNTA on Filtered Visible Cells #9
Hi Aladin, Thanks for reply. However, the formula when entered on a sampl section in Column "T" only gives me a sequential count starting fro eg: 0-40 down Column "T". It is not returning the expected result based on the text in Column "V" that I've enclosed in quotation mark within curly brackets for the array element: {"Executive", "Trainees" "Manager", "Admin"}. Further assistance appreciated. Thanks Tin� Aladin Akyurek Wrote: > Tin�, > Just picking out the above filtered out question: The answer is yes ...

Multiple instances of Dynamics CRM 4.0 on one domain
Hi, Does anybody know if this is possible. We had CRM installed on one server on the network and then attempted to install another instaance on a Virtual Server, but on the same domain. both connecting to their own local SQL Server DBs). The version on the Virtual Server works OK but now the original version doesn't work. If you try to browse it as any user you get the message: Access Error The system could not log you on. This could be because your user record or business unit you belong to has been disabled Any help would be appreciated Thanks On Jun 30, 12:05=A0pm...

Multiple Symbols
Money 2004 Standard: I'm trying to add two separate entries for the same fund but for different account holders. Both are under the same funding institution. when I try to do this, I get an error telling me I have the symbol already in use. How do I get around this? I don't want to group them together, because they are funds that are separated between my wife and I. Thanks, -- Bill See http://umpmfaq.info/faqdb.php?q=59. "BigBillyDE" <BigBillyDE@discussions.microsoft.com> wrote in message news:69FA1419-DEB6-4A2E-9EAF-FF1216DB42A3@microsoft.com... > Mo...

Combining multiple columns of text
I have a file of addresses. Each individual line of the address is in a different adjacent cell. I would like to be able to combine the text into one cell that is separated by commas, and or spaces. Is there an easy way to achieve this -- Amber Are you making labels? If yes, then keep your data the way it is. It's much easier to combine the values than to separate it. Use excel as your data base and use MSWord as the application to make the mailing labels. You may want to read some tips for mailmerge. http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/F...

counta #2
Hi According to microsoft.com and the online help =COUNTA(A1:A7,"Two") should give you the number of rows containing "Two", but all you get when you try the above is a Formula error ! What do I miss ? Thanks in advance. Steen You should be using COUNTIF instead. =COUNTIF(A1:A7,"Two") tj "rabol" wrote: > Hi > > According to microsoft.com and the online help =COUNTA(A1:A7,"Two") should > give you the number of rows containing "Two", but all you get when you try > the above is a Formula error ! > > Wha...

Multiple Outlook Clients
Hi, ive a short question about the outlook client. Weve just installed three CRM 3.0 Servers in our Company. I would like to integrate them alltogether in my outlook. Is it possible to install three outlook clients on one machine. The Setup application says no - but is there a workaround? regards Andreas Doubt it "Auderer Andreas" <Auderer Andreas@discussions.microsoft.com> wrote in message news:ADA92C1F-382D-4E57-863A-66DEDF3E10C2@microsoft.com... > Hi, > > ive a short question about the outlook client. Weve just installed three > CRM > 3.0 Servers in o...

If formula
I want use multiple discriptions in my spreadsheat to indicate when a individual is off. How do I add this into my formula. I have the "off" in there now and it works fine. However, I want to add the text "CE", "ML", "VAC" to equal 0. =IF('2007 HRS'!B12="OFF",0,'2007 HRS'!B12*'2007 HRS'!$E$44) thanks One way: =IF(ISNUMBER(MATCH('2007 HRS'!B12,{"OFF";"CE";"ML";"VAC"},0)),0,'2007 HRS'!B12*'2007 HRS'!$E$44) -- Max Singapore http://savefile.com/proje...