Sumproduct Help 02-25-10

Let's say I have in column A a series of strings, and in Column B either 
"Yes" or "No".  I would like to count the number of occurrences where I have 
a certain sub-string (say "XYZ") in the cell in column A, and where the cell 
in column B in the same row is "No".  Do this for rows 2 to 100.

I can use =COUNTIF(A2:A100,"*XYZ*") to get the first count, but I can't 
figure out how to combine it with the second criterion of "No" in column B.  
Is this a good time to use SUMPRODUCT, which I have yet to learn?

Thanks,

Eric

0
Utf
2/25/2010 10:01:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
724 Views

Similar Articles

[PageSpeed] 31

=SUMPRODUCT(--(ISNUMBER(SEARCH("xyz",A2:A100))),--(B2:B100="no"))

SEARCH is not case sensitive, if you need case sensitive searching, replace 
it by FIND.


-- 
Regards!
Stefi



„EricG” ezt írta:

> Let's say I have in column A a series of strings, and in Column B either 
> "Yes" or "No".  I would like to count the number of occurrences where I have 
> a certain sub-string (say "XYZ") in the cell in column A, and where the cell 
> in column B in the same row is "No".  Do this for rows 2 to 100.
> 
> I can use =COUNTIF(A2:A100,"*XYZ*") to get the first count, but I can't 
> figure out how to combine it with the second criterion of "No" in column B.  
> Is this a good time to use SUMPRODUCT, which I have yet to learn?
> 
> Thanks,
> 
> Eric
> 
0
Utf
2/26/2010 7:51:01 AM
Stefi,

That did the trick!  Köszönöm!.  Now I have one example of SUMPRODUCT to 
learn from.

Regards,

Eric


"Stefi" wrote:

> =SUMPRODUCT(--(ISNUMBER(SEARCH("xyz",A2:A100))),--(B2:B100="no"))
> 
> SEARCH is not case sensitive, if you need case sensitive searching, replace 
> it by FIND.
> 
> 
> -- 
> Regards!
> Stefi
> 
> 
> 
> „EricG” ezt írta:
> 
> > Let's say I have in column A a series of strings, and in Column B either 
> > "Yes" or "No".  I would like to count the number of occurrences where I have 
> > a certain sub-string (say "XYZ") in the cell in column A, and where the cell 
> > in column B in the same row is "No".  Do this for rows 2 to 100.
> > 
> > I can use =COUNTIF(A2:A100,"*XYZ*") to get the first count, but I can't 
> > figure out how to combine it with the second criterion of "No" in column B.  
> > Is this a good time to use SUMPRODUCT, which I have yet to learn?
> > 
> > Thanks,
> > 
> > Eric
> > 
0
Utf
2/26/2010 2:21:03 PM
Reply:

Similar Artilces:

How do I add the last 10 cells
If I have a column labelled, say, temperature, that is often being added to, how do I add up the last 10 cells. I want excel to do it for me automatically. I want a formula that will find the last filled-in (non blank) cell in a column and then add up the 10 entries above that. Assuming your data starts in A10, then in A9 perhaps, try the following:- =SUM(OFFSET($A$10,COUNT(A10:A9995)-10,,10)) If your data started in A15, formula would be:- =SUM(OFFSET($A$15,COUNT(A15:A10000)-10,,10)) This assumes you have no other data below this range in that column (Or at least within the COUNT range)....

Event ID:25
The reminder service did not start. Now I know from reading other posts that I am not the only one seeing this error, there is no information at MS regarding this yet it clearly is an issue for some. Does anyone have an idea about how to fix this??? ...

(APPZ) Video2Brain.Autodesk.Inventor.2010.Workshops.fuer.Fortgeschrittene.GERMAN-Substance
=ybegin part=1 line=128 size=22368 name=Video2Brain.Autodesk.Inventor.2010.Workshops.fuer.Fortgeschrittene.GERMAN-Substance.par2 =ypart begin=1 end=22368 zk|\*zu~*******��Z���n��t��������|��[�3"��ͥ}zk|J\XZ*p���n���V�T)��ZS1%�ژ|�t/6�$�C�U����CP.*��^" �^.S�S6**:-****�����\l� ���Xk�������Xs�������X\Z[ZX���������X����Xp���������������Xqo|wkxW}��������X����Z[X���**zk|\*zu~�4******�`j~+��'��,�PVC���|��[� 3"��ͥ}zk|J\XZ*sp}m****V�T)��ZS1%�ژ|���\=@����%ʭ����|�3�\Ͽ���kt�ㅬZqM���X����4�y��j&b�8��] I�=J�u��mg1Cr�����)f'Ӿ *��}�Q�F�\>�&o_<P�֝�\\...

Need help with a query
Let me start by saying that I have very little experience in Access. I've learned what I know by "tinkering" around in it and a lot of the terminology I've seen on these boards is above my head. Here's the background to my question. I created an employee database that consists of one massive form with several tabs and on each tab there are several subforms. The main form was created from the Employee Header table and the subforms were created from queries from tables. The idea is when you open the form, you see an employee's basic info on the top ...

EFT for Payables - GP 10
In version 9 or before, a client could select all cheques for payment, and the system would create cheques for vendors without EFT info, and create the EFT file for vendor with EFT. I cannot do this anymore (in GP 10). On the batch setup screen when you choose computer cheque, there is an option for payment type - Cheque or EFT. I have to create an EFT batch and use select cheques, then create a cheque batch, and process them separately. If I forgot to do the EFT batch first, then all vendors would get paid by cheques. If this is the case, this will cause grief to a lot of clients who are...

macros #10
If I have a macro that copies A1 and pastes it into b1, how can I change the macro to paste it into any selected cell? Hi HOHN, Try one of these... Sub S_Copy() ActiveCell.Value = Range("A1").Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Value = Range("A1").Value End Sub Where you will have to activate the S_Copy() macro with a button or such and the Selection_Change macro will copy A1 th the cell you click on. HTH Regards, Howard "JOHN" <JOHN@discussions.microsoft.com> wrote in message news:34A28B2B-D959-46...

Changing Account Distributions in GP 10
Hello, We have installed the SP 1&2, and the feature pack. When we are doing a voucher transaction and/or a reciept againt a PO, and want to send the expense to more than one account, we can't figure out how to do it. If anyone can help out we would greatly appreciate it! Thanks, Michelle Michelle: Search on this topic - 'Changing PURCH account at summary level GP 10 Options' This will explain the change in GP10 and some suggestions for work- arounds. Hope this helps, Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com Frank, Where shoul...

Pivot table help #11
I needed help with a Pivot table. In the attached excel sheet i am trying to use the Creatiion date (coloum X)as one of the filters in the pivot table. Could someone please guide me as to how i can filter with the Creation date as one of the pivot tables. Thanks Sammy ...

help: how protect formulas in workbook?
Can we locked cells with formulas from editing in one step? When I select cells with formulas in all worksheets by selecting all worksheets and give properties "locked" in Format -Cells-Protection tab and when I try to protect worksheet by Tools-Protection-Protect Sheet is not available. Can we protect all worksheet in workbook in one step or we must do it sheet by sheet? Thanks. spanic -- spanic ------------------------------------------------------------------------ spanic's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5105 View this thread: ht...

Bar chart help wanted
Hello to everybody ! I have this kind of data: TIME VALUE 0:00:00 0 0:00:34 1 0:00:35 1 0:01:00 0 0:01:30 0 0:01:33 1 0:01:55 1 0:01:59 0 0:02:00 0 0:02:10 1 0:02:15 1 0:03:00 0 0:03:05 0 0:03:07 1 0:03:55 1 0:08:00 0 and I need to create bar chart that would have a bar of the hight equal to 1 at each time value where the "value" in table (Y-axis) equals one (and also between "one"s values). In all other time values the Y value is zero ! Excel bar chart seems to omit all values that are not in the dataset ! I need to include also those time values that are not expli...

Find the 10% value
Hi, I have a monthly report that contains data for a varying amount (each month) of users across several column. For each column I need to use conditional formatting to highlight the worst 10% for each measure. Currently I'm doing this manually by sorting the column ascending, then if there are 15000 users, look at the value corrensponding to line 1500, enter this in a cell which I then reference in my conditional format. What the best way to do this by formula? -- Rich http://www.rhodes-lindos.co.uk On Thu, 4 Mar 2010 20:41:46 -0000, "Rich" <REP...

help. outlook 2007. not listing contacts
Hi all, i have a small problem in that some of my contacts are not listed in my contacts, example: in address bar type 'b' get a list of all my 'b' contacts, barbara, ben, bill, etc but none of them appear in my address book, or contacts. only listed if i add them to address bar. My question is: where are they stored cos I want to delete one or more. I think they are addresses i have only replied to and never 'created' but have used for new messages, by just typing the first letter and selecting from there. Help please. Many thanks in advance. Lepak Lepak Nera...

Help with profiles .pst files and roaming profiles
Hi all and thanks in advance for the future answers; Well, we have a enviromente that goes like this: 1 Domain Controller 1 Mail Server running KERIO MAIL SERVER 2 Terminal Servers running CITRIX PS 4.5 1 Server running the citrix console The problem: We have like 50 users now that connect to servers A or B and have their roaming profile working fine, all files and configs come and go, with the exception of the OUTLOOK profile. When i had to add SERVER B to the system, i had to copy users folders from server A and apply the same permissions one by one, otherwise outlook would not work and ...

HELP! Money Files and Backup files lost!!
I had a computer guy work on my computer and asked him to consolidate folders because we had folders all over the place and I wanted them all in a centralized location. As a result, Money can't find my files. I tried doing a search for *.mbf & *.mny files to no avail. The files that show up are EMPTY!! AND as luck would have it, BOTH my backup floppies are corrupt!! Can anyone help me find my files? The computer guy insists that he deleted nothing. I'm using Microsoft Money 2001. Any help would be GREATLY appreciated as we're talking about 8 years of finanacial d...

Spread Sheet Help again
Here it is hope someone can help me out one more time? I sell something for $25.00 or lower the fee is 5.25% ($1.31), But if its more $25.01 to $1,000.00 fee is 5.25% of the initial $25.00 ($1.31), plus 2.75% ($0.68)of the remaining closing value balance ($25.01 to $1,000.00) Cell H1 is $25.00 then Cell J1 is $1.31 (5.25%) Cell H1 is $25.01 then Cell J1 is $1.31 (5.25%)plus 2.75% or $1.99 Can I have them in the same cell somehow is this possable? Thanks for the help -- califman1961 ------------------------------------------------------------------------ califman1961's Profile: http:/...

Yes/No data field help
Hello- I have several fields in Access that are Yes/No data types but I want them to be 0,1 in the TABLE and have a check box in the Form. How do I do this? When I choose the Yes/No Data Type in the table it defaults to the checkbox but I am guessing I can change this to text I still get yes and no when I do Combo box I can change the choices to 0,1 but I am unsure then how to change the combo box to a checkbox in the form. Thanks! If the datatype is YES/NO then you can just delete the combo box and then drag and drop a checkbox on to your form. Then, in the checkbox's record...

help!!! #2
Can somebody tell me the name and location of the file that holds the address book? I have reinstall the outlook and imported the contacts from a comma delimited file, all of the contacts appear on the contact folder but when I open a new message window and click on the TO: button to add recipients a message saying that the address book could not be found and after that it shows a window asking for the proper list to show in a drop down list; that drop down list says 3 times the word "contacts" and if I select the second then all the contacts would appear but I don't kn...

Office Help and Embeded links
Strange problem on one machine. Help files work, however, like the show all link or any of the other links that show up, clicking on them does nothing. All other machines are working with not problem, just this one. Uninstall/reinstall does not work. It is not the customer feedback problem or the default browser. Any ideas? ...

Help rank features for future Dynamics GP releases
Each year, we ask customers to give us valuable insight into the enhancement of our products. When you give us your feedback and opinions, you help us refine our software to make it even more customer friendly. Please take a few moments to visit the URL below and complete the Product Advisory Board Survey now. This forum is an important way for us to get your opinion on our products, but this special opportunity is available for a limited time. https://mbs.microsoft.com/customersource/pabs -- This posting is provided "AS IS" with no warranties, and confers no rights. We w...

Chart display help
How can I have the chart bars display the percentages and the data labels be the raw numbers? Is this possible? Hi, By default bar charts don't display percentages, however, you could create custom data labels by combing a calculation of the percentage with the actual value in another range and the using a program like the free XL Chart Labeler to pick up that range for the data labels. http://www.appspro.com/Utilities/ChartLabeler.htm Suppose the numbers that are your Values are in C2:C10, create a new column by entering the following formula in D2 =C2/SUM(C$2:C$10) Copy this fo...

[ANN] Entourage Help Page revised
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3167822367_9059007 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit Check out the new revised Entourage Help Page. > <http://www.entourage.mvps.org/> For easy searching check out: > EHP Site Map <http://www.entourage.mvps.org/site/map.html> Start your search of the FAQs here: > <http://www.entourage.mvps.org/faq/atoz/a.html> Exchange Support for Entourage 2004 > > <h...

Help window in Leopard stuck behind menu bar
Since installing Leopard when I open the Help window in any Office program it opens at the top of the desktop and seems to be behind the menu bar. The red, yellow and green buttons are missing/ not visible as well as the rest of the window header. It can't be moved or manipulated in any way. The real problem occurs when trying to close the window. Using keyboard short cuts it first acts on the active window. So in Excel it will close the workbook window when hitting command-W. Then it will work to close the help window. This slows everything down. Any help would be appreciated. Mike Go in...

help!
I use MS Money every few weeks to update my after tax accounts. It does a good job. When I tried today, it won't connect to any three of my accounts. It just says "update in process" next to all of my accounts. I have the password in the promt box (I read the note on merging accounts).. do you think that is the fix? Any help would be appreciated. I was unable to update my accounts late Friday night, but OK Saturday afternoon, then unavailable again late Saturday. This is usual for Bank of America as they do maintenance on weekends. This MAY be your issue or it could...

multiple environments 02-07-06
When implementing ERP systems it is always easy to work with multiple environments such as a test, training and live environment. We tried to simulate this with several business units, but apparently test accounts entered or imported into the test Business Unit also also visible in the Live Business Unit. Does anyone have suggestions how to achieve this? -- Tony Rook Independent Concultant The Netherlands I do this with completely separate networks... There is the live network and then the test network which is a completely separate AD network. This allows us to test all aspects of th...

hola 10-29-07
hollllallalalalalalala ...