COUNTIFS - 2 different scenarios to count

Hi:

I have tried many variations of different nested formulas and have had no 
luck solving this one. 

Here are the two scenarios that I am trying to accomplish:
Scenario 1:
Count 1 if the following criteria are met on a single line of data:
Col F contains text (is not blank)
Col G, H, and I do NOT contain "Dropped"

Scenario 2:
Count 1 if the following criteria are met on a single line of data:
Col F contains text (is not blank)
Col G, H, I all contain the word "Approved" or "N/A" - the tricky part is 
that there are several different variations of Approved so I tried using the 
wildcard "*Approved*" but it doesn't seem to work in this particular formula.

The two scenarios are separate from eachother. Any help is appreciated! 
Thanks.
0
Utf
2/4/2010 3:36:15 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1130 Views

Similar Articles

[PageSpeed] 14

Scenario 1:
=SUMPRODUCT(--ISTEXT(F2:F10),--ISERROR(SEARCH("dropped",G2:G10)),--ISERROR(SEARCH("dropped",H2:H10)),--ISERROR(SEARCH("dropped",I2:I10)))

Scenario 2:
=SUMPRODUCT(--ISTEXT(F2:F10),
(ISNUMBER(SEARCH("approved",G2:G10))+ISNUMBER(SEARCH("n/a",G2:G10))),
(ISNUMBER(SEARCH("approved",H2:H10))+ISNUMBER(SEARCH("n/a",H2:H10))),
(ISNUMBER(SEARCH("approved",I2:I10))+ISNUMBER(SEARCH("n/a",I2:I10))))

Note that the SEARCH function is non-case sensitive. If you want to switch 
it to case sensitive, use the FIND function. If column F may contain values 
and not just text, change that arguement to:
--NOT(ISBLANK(F2:F10))

Finally, unless using XL2007, you can't callout entire columns using 
SUMPRODUCT.
-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Roady" wrote:

> Hi:
> 
> I have tried many variations of different nested formulas and have had no 
> luck solving this one. 
> 
> Here are the two scenarios that I am trying to accomplish:
> Scenario 1:
> Count 1 if the following criteria are met on a single line of data:
> Col F contains text (is not blank)
> Col G, H, and I do NOT contain "Dropped"
> 
> Scenario 2:
> Count 1 if the following criteria are met on a single line of data:
> Col F contains text (is not blank)
> Col G, H, I all contain the word "Approved" or "N/A" - the tricky part is 
> that there are several different variations of Approved so I tried using the 
> wildcard "*Approved*" but it doesn't seem to work in this particular formula.
> 
> The two scenarios are separate from eachother. Any help is appreciated! 
> Thanks.
0
Utf
2/4/2010 4:23:01 PM
One follow-up question regarding your last comment, "Finally, unless using 
XL2007, you can't callout entire columns using SUMPRODUCT" - So, the grid 
that I am creating could potentially be used by both 2003 and 2007 users. 
Instead of going until row 65536, can I just go to 65535 and then the formula 
will work properly?
What can I do to make sure it will work for both versions?

Thanks! 

"Luke M" wrote:

> Scenario 1:
> =SUMPRODUCT(--ISTEXT(F2:F10),--ISERROR(SEARCH("dropped",G2:G10)),--ISERROR(SEARCH("dropped",H2:H10)),--ISERROR(SEARCH("dropped",I2:I10)))
> 
> Scenario 2:
> =SUMPRODUCT(--ISTEXT(F2:F10),
> (ISNUMBER(SEARCH("approved",G2:G10))+ISNUMBER(SEARCH("n/a",G2:G10))),
> (ISNUMBER(SEARCH("approved",H2:H10))+ISNUMBER(SEARCH("n/a",H2:H10))),
> (ISNUMBER(SEARCH("approved",I2:I10))+ISNUMBER(SEARCH("n/a",I2:I10))))
> 
> Note that the SEARCH function is non-case sensitive. If you want to switch 
> it to case sensitive, use the FIND function. If column F may contain values 
> and not just text, change that arguement to:
> --NOT(ISBLANK(F2:F10))
> 
> Finally, unless using XL2007, you can't callout entire columns using 
> SUMPRODUCT.
> -- 
> Best Regards,
> 
> Luke M
> *Remember to click "yes" if this post helped you!*
> 
> 
> "Roady" wrote:
> 
> > Hi:
> > 
> > I have tried many variations of different nested formulas and have had no 
> > luck solving this one. 
> > 
> > Here are the two scenarios that I am trying to accomplish:
> > Scenario 1:
> > Count 1 if the following criteria are met on a single line of data:
> > Col F contains text (is not blank)
> > Col G, H, and I do NOT contain "Dropped"
> > 
> > Scenario 2:
> > Count 1 if the following criteria are met on a single line of data:
> > Col F contains text (is not blank)
> > Col G, H, I all contain the word "Approved" or "N/A" - the tricky part is 
> > that there are several different variations of Approved so I tried using the 
> > wildcard "*Approved*" but it doesn't seem to work in this particular formula.
> > 
> > The two scenarios are separate from eachother. Any help is appreciated! 
> > Thanks.
0
Utf
2/9/2010 10:06:01 PM
Reply:

Similar Artilces:

different formatting, same cell
Say the date that a certain event occurs (2/17/07) is in cell A1. I would like to have cell B1 display: Completed 2/17/07 If I use the formula ="Completed "&A1, it displays: Completed 39130 Is there another way to do this? To Excel, dates are just numbers, so you need to tell it to convert the number (date) to text how to display that text. Try something like this: ="Completed "&TEXT(A1,"MM/DD/YYYY") Does that help? *********** Regards, Ron XL2002, WinXP "Matt" wrote: > Say the date that a certain event occurs (2/17/07) is in cell A1....

List of different values in data area
Hi excel specialists, How Can I automatically get the list of different values from the dat area and to find out their frequence? INDIVIDUALLY MEASURED VALUES: 3,5 4 3,5 4 3 3,5 4 4 3 3,5 4 3 3,5 3 3 3,5 4 4 Thanks for your help in advance. Balcovja -- balcovj ----------------------------------------------------------------------- balcovja's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2623 View this thread: http://www.excelforum.com/showthread.php?threadid=39572 Take a look at FREQUENCY in Hep -- HTH RP (remove nothere from the email address if m...

CountIf Statement
Can anybody help? I am creating a formula in a cell of a spreadsheet which will Count all in stances of "distribution centre" within a column This is achieved using the =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") I was wondering whether it was possible in excel to use a statemen like =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") and (nex expression) any ideas -- Message posted from http://www.ExcelForum.com You can use sumproduct to count with more than one condition multiple ranges =SUMPRODUCT(--(Range1="GROUP&...

Counting rows that fit within a range
I've got what seems to be a simple problem to do by hand (if it weren't for the fact that the table in question isn't exactly small). So I was hoping I could do it (somehow) in an Excel spreadsheet. What I have, is a column of numbers (to one decimal point), assumed to be sorted in descending order. I want to have another column (calculated by Excel) that will count how many rows have a larger value (in the first column) than it, but only up to a certain range (by adding a constant to the value of that row). For example, here's some data, with the first column supplied by ...

Summary of Difference between dates in years, months, days
I need to calculate the difference between 2 dates and then total them. Here's what I have so far: From To Length of Service 01/09/2003 31/01/2010 6y 4m 30d 01/06/2000 30/11/2002 2y 5m 29d Total of Service: ?????????? I've used the following formula to calculate the total days worked: =DATEDIF(A4,B4,"Y")&"y "&DATEDIF(A4,B4,"ym")&"m "&DATEDIF(A4,B4,"md&...

IF, COUNTIF
Can someone explain why these two expressions differ referring to the value 5? IF(A2:A10<5 etc. ) but COUNTIF(A2:A10,"<5") requires a comma and quotes Wouldn't it make sense for them to be the same? Just wondering. For us it would seem to make sense, but for the computer not so much :-) My understanding is that in the IF statement the <5 is a Comparison Operation which is a part of the 1st Function Argument whereas in the COUNTIF it is a Criteria Reference supplied as the 2nd independent Function Argument. If it stood alone as simply <5 it would ...

Countif ...
I have a column with phone numbers. I want to count how many phone numbers have the area code "214" and "972". Thanks, the first formula worked. "Jason Morin" wrote: > The formula depends on how your phone numbers are formatted and whether they > are text or actual 10 digit numbers. For example, if they are text and you > simply need the first 3 numbers in the cell, try: > > =SUMPRODUCT(--(LEFT(A1:A10,3)={"214","972"})) > > If the area codes are enclosed in parentheses, you could use: > > =SUM(COUNTIF(A1:A10...

Count consecutive values down a column
I have many tick records from live market data, in many separate files (one for each stock symbol). I am trying to make a new file of 2 fields; date & a count of the number of ticks (or records in the original file) for each date. The records in the original files show records (ticks) for any one date as ranging from 1 to well over 1000. The VB I've attempted to write has struck maybe it's biggest error where there is only one recortd (tick). Otherwise I've managed to account for importing the original text via macro and exporting the same (with thanks to Chip Pearson), ans a...

Database size and Mailbox size differences
Hi, I just did an off-line defragmentation. The priv1.edb is now 38.7 GB, and the priv1.stm is 14.9 GB. The total backup of the information store and the site replication service is about 56 GB. However, the total mailboxes size from Exchange System Manager is about 33 GB. What is the .stm file, and how do I reduce the backup to the size of the total mailbox? Thanks, The totals in the GUI don't include retained deleted items. -- Ed Crowley Celebrating a decade of Exchange peer support "Anonymous" <Anonymous@discussions.microsoft.com> wrote in message news:81A5...

different versions
I created a calendar @ work with a higher version of Publisher. I am trying to access the file @ home, but I have 2000 and it says it can't open it. What can I do? You will have to save the calendar at work as a Publisher 2000 document. -- Mary Sauer http://msauer.mvps.org/ "jabrjawz" <jabrjawz@discussions.microsoft.com> wrote in message news:57F19583-7E3D-4EF3-B384-ABDA85A33AF6@microsoft.com... >I created a calendar @ work with a higher version of Publisher. I am trying > to access the file @ home, but I have 2000 and it says it can't open it. > What...

Colour code from different servers
-- I collect mail from 2 different servers and I was wondering if it is possible to have them show in the IN BOX in different colours. I know I can select different senders by colour but I want to show all senders that came from each server. Can anybody help Pete A "Pete A" <PeteA@discussions.microsoft.com> wrote in message news:EB77DCA6-A07E-47E9-AD04-62C8B6BC1193@microsoft.com... > > -- I collect mail from 2 different servers and I was wondering if it is > possible to have them show in the IN BOX in different colours. I know I > can > select different se...

Criteria/CountIf and Pivot Table
Hello: My data looks like this ID Date CWA Amount 1 08/12/2006 0 $0.00 2 08/13/2006 1 $10.00 3 08/14/2006 1 $20.00 4 08/01/2006 0 $0.00 Based on the above data, I want to create a pivot, by month (I know I only have Aug here) to show the following: Sum of CWA Count of CWA Percent of cases that were submitted with CWA. The CWA is an indicator field that looks at another field (not listed here) to determine if there is cash in a...

Same .pst Files Looks Different on other PC
Hello, I have a weird problem. I have a small network at home with 1 desktop and 1 (wireless) laptop. I have installed MS Office XP on both computer including all the servicepacks and updates (both Outlook version numbers 10.4712.4219 SP-2). I share my document folder which is on the desktop, so I can access it through the laptop. When I open the .pst file on my laptop, Outlook handles the full-day appointments differently. It moves them up one hour, i.e. makes the appointments start at 23.00 hours the night before (and then they last till 23.00 hours on the right day). When I double-click th...

Countif
I am trying to solve the following problem i) cells A1 to A10 have either M or F (male or female) ii) cells B1 to B10 have either (grades) A B or C iii) I want (eg cell B13) to state the number of female students who scored A... and cell B14; how many female students who gained B etc I have tried various things including countif, sumif etc. It seems like a straight forward problem but the solution evades me! Eddie =SUMPRODUCT((A1:A10="F")*(B1:B10=LEFT(ADDRESS(ROW(B1),ROW(A1),4),1))) in Cell B13 and filled down to Cell B15. Alan Beban Eddie wrote: > I am trying to solve ...

Counting the number of entries in a column
Hi! I have area numbers in column A and the number of produkts ordered in column B I want to count the number of entries in a particular area bearing in mind I just want to see the number of entries rather than sum them up like SUMIF. Is there a convenient formula which would allow me to do this? Thanks for any help Hi, I assume your product is in column B and the product name is XX =countif(B1:B100,"XX") Change range and product name to fit your needs you can have as well the product you want to count entered in a cell let's say C1 in that case you can...

IF two different conditions are met #3
The array isn't working eithe -- Anna ----------------------------------------------------------------------- AnnaV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=961 View this thread: http://www.excelforum.com/showthread.php?threadid=27065 Have you tried Jason's suggestion? -- HTH RP "AnnaV" <AnnaV.1eeaoo@excelforum-nospam.com> wrote in message news:AnnaV.1eeaoo@excelforum-nospam.com... > > The array isn't working either > > > -- > AnnaV > -----------------------------------------------------------------...

CountIf with And
Novice Excel user on Excel 2003. I have a yearly data input sheet to track codes. I use the date (fomatted as 1/01/2009) and a code. I want to track the codes in a mothly chart for ease in summarization and improvment tracking. The codes are used to define a department and an error in that department (code 1a means - AR department and error a). I need to pull and total the amount for each code for each month. After searching for a while it seems that using CountIf with And does not work. Any suggestions would be greatly appreciated. -- Mike =SUMPRODUCT(--(MONTH(date_range)=4),...

Access 97 to 2003
I'm converting a legacy db (not my code!) I have a form with a linked subform (master/child) with code in the subform load event The subform code tests the value of a field in the subform and reacts accordingly Sometimes the child subform has data, sometimes not, depending on the record in the main form There seem to be two differences (1) In Access 97 the load event seems to only fire if there is data to show in the subform - if no records, then no event In Access 2003, it seems the event fires every time (2) In Access 2003, in cases where there IS data for the subform, I get an error wh...

Counting text characters in a row
This problem is likely very simple however I'm new to speadsheets. I've created a spresdsheet to record attendace at work. in each row have an "H" or "S" to denote holiday or sickness respectively, agains each name. How do I count the number of "H's" in a row to tot up the number o days holiday taken. Sorry if this is very basic, but so is my spreadsheet knowledge. Ala -- Message posted from http://www.ExcelForum.com Hi ;), Dargo wrote: > *This problem is likely very simple however I'm new to speadsheets. > I've created a spresdsheet...

Excel countif and
is there a "and" condition if you use countif or sumif? example; 1 c 1 1 c 2 2 < countif(b1:b4,"=c")and(a1:a4,"=1") Hi! Try this: =SUMPRODUCT(--(A1:A4=1),--(B1:B4="C")) Biff "KEN" <KEN@discussions.microsoft.com> wrote in message news:02a801c54ae1$3371a4f0$a401280a@phx.gbl... > is there a "and" condition if you use countif or sumif? > example; > > > 1 c > 1 > 1 c > 2 > 2 < countif(b1:b4,"=c")and(a1:a4,"=1") > ...

Countif() formula
Hi I have a column in Excel, D7:D200, filled of real numbers. Also the cell F3 includes a number (variable cell). I would like to count how many numbers of the column D7:D200 are >F3, but in successive ranges of D column. (That means how many numbers in: D7:D8 > F3, D7:D8 > F3, D7:D9 > F3…………….and finally D7:D200 > F3). To do that I have applied in E7 the formula: Countif(D7:$D$7 ; “>F3”) and expand down to E200. Unfortunately the above formula returns zero in all applied cells. If I change the F3 in the formula using a stable number (2,3,4 etc) the formula works, but...

counting number of particular items in a list
I am making a monthly file of all vehicles sold during the month on day to day basis. I want to have a count of particular type of vehicles i. e. s number of saabs, and how many 9-2 or 9-3's. Or chevroletss and how many of that trail balzers etc. File attached. Thanks much for the help. vik kamdar vikkam@hotmail.com +-------------------------------------------------------------------+ |Filename: pr_monthly_report_forum.zip | |Download: http://www.excelforum.com/attachment.php?postid=3559 | +------------------------------------------------------------...

counting amount of visitors
Hello, I made a website and it is working fine. Now I would like to insert a tool with which I can see, how many visitors have been visiting my side. How to do this? Do a google search for "website counters" and you'll get hundreds of possibilities. -- Don "May your shadow be found in happy places." (Native North American) "Smeer" <Smeer@discussions.microsoft.com> wrote in message news:F5C27A8F-EEFD-4E5D-A500-FA82581D8D25@microsoft.com... > Hello, > I made a website and it is working fine. Now I would like to insert a tool > with which...

Count(*) with different period
Hi All, The query I try to make is really difficult for me. I try to don't make it with a personnal function but I'm not sure that is possible. I have a Table with the following data (nb: format date dd/mm/yyyy) Contract Number Start End 00001 01/01/2005 02/05/2008 00002 01/03/2004 21/08/2007 00003 01/03/2003 21/10/2007 00004 07/05/2003 21/11/2007 00005 01/12/2007 21/10/2010 I want to count how many contracts are activ for each month of 2007 So the res...

Reply goes from different acct than received
One of my users is having this rather strange problem. She has 3 email accounts configured in Outlook with different email addresses (from 2 domains). Sometimes (not all the time) when she replies to a message that was sent to 'account1' (her default acct as well) the message will show it will be sent from 'account2'. She has to (when remembered - we don't remember all the time) change the account before sending. It only does it some of the time. I'm certain it's doing it because I checked the email header - it shows being sent to account1. I've sea...