Converting COUNTIFS To 2003

Jul 6, 2009

I have the following excel formula:
=LOWER(IF(($H2&":"&$M2&":"&$N2&":"&TEXT(COUNTIFS($H2:$H$2,$H2,$M2:$M$2,$M2,$N2:$N$2,$N2),"0000"))=": ::0000","",$H2&":"&$M2&":"&$N2&":"&TEXT(COUNTIFS($H2:$H$2,$H2,$M2:$M$2,$M2,$N2:$N$2,$N2),"0000")))

I need to convert the COUNTIFS into something that is Excel 2003 friendly.

View 2 Replies


ADVERTISEMENT

Converting Countifs To 2003 Formula

Oct 6, 2008

I am having trouble converting the following Countifs formula developed in 2007 to work in 2003 Excel.

Basically, I want to count the frequency of "N" in 'Data_Q2 2008'!$S$2:$S$9806, where 'Data_Q2 2008'!$B$2:$B$9806 = 'Metric Upload'!C2.

'Data_Q2 2008'!$B$2:$B$9806 is a list of physicians. 'Metric Upload'!C2 is a pick list for selecting the physican that is in the larger data set using Data Validation.

View 14 Replies View Related

Converting CountIFS Formula To Excel 97-2003

Jan 22, 2014

I have attendance tables that has column Y showing the shift to be worked and column Z showing any absentees. I used the following formula to count the number of people off per period.

{=SUM(COUNTIFS($Y$5:$Y$29,AM,$Z$5:$Z$29,$W82),(COUNTIFS($Y$36:$Y$60,AM,$Z$36:$Z$60,$W82)))}

in the above formula AM relates to a list of morning shifts.

The problem I have now encountered is that the system at work is still using excel 97 - 2003 which does not recognise Countifs.

way to adapt the formula to be compatible with Excel 97.

View 7 Replies View Related

Replacement Of COUNTIFS Within 2003

May 29, 2008

Assist me in getting the following statement into Excel 2003 format - it is working OK in Excel 2007.

=COUNTIFS('Sheet 1'!$AB:$AB,"True",'Sheet 1'!$F:$F,'Sheet 2'!B13,'Sheet 2'!$J:$J,">="&H2,'Sheet 1'!$J:$J,"

View 9 Replies View Related

Countifs Alternative On 2003 With Multiple Cells And Worksheets

May 18, 2009

I created the following in Excel 2007 but need to come up with a replacement formula in 2003.

=COUNTIFS('Data High'!AM2:AM999,"0",'Data High'!AN2:AN999,"Non Corp",'Data High'!AL2:AL999,"No")+COUNTIFS('Data Medium'!AI2:AI2000,"No",'Data Medium'!AJ2:AJ2000,"0",'Data Medium'!AK2:AK2000,"Non Corp")+COUNTIFS('Data Low'!AA2:AA5000,"No",'Data Low'!AB2:AB5000,"0",'Data Low'!AC2:AC5000,"Non Corp")

With this spreadsheet i pull data straight from a database and place it in to 3 separate worksheets. The first worksheet which is the summary sheet then updates all the totals of certain occurrences. The above formula is the same format for all fields in the summary with a few variations. But they all have the following in common. They draw data from all 3 worksheets and total it all up, and each countifs looks at 3 different cells.

In terms of replacing it i have tried to use something like =SUMPRODUCT(--('Data Low'!AA2:AA5000="No")*('Data Low'!AB2:AB5000="0")*('Data Low'!AC2:AC5000="Non Corp")) but don't seem to be getting it right.

View 2 Replies View Related

Converting Code From 2007 To 2003

Jun 24, 2008

I created this code in Excel 2007 now i want to put it on my other users machines they are using Excel 2003 and i keep getting debug errors can someone help

Sub setup()
' Keyboard Shortcut: Ctrl+Shift+C
'add sheet
Sheets.Add After:=Sheets(Sheets.Count)
'format headers
With Range("A1")
.Value = "Job Name"
.Name = "Arial"
End With
With Range("A2")
.Value = "Quote #"
.Name = "Arial"
End With
With Range("A3")
.Value = "Job #"
.Name = "Arial"

View 9 Replies View Related

Retain 2003 Code In 2007 And Back To 2003

Aug 29, 2008

I've created a workbook with code to email it to a reviewer then back to the originator when reviewed (along with other editing functions etc.). The problem - The originator is working in Excel 2003, the reviewer in 2007. The macros work great until the reviewer sends it back. I have not been able to figure out which FileFormat:= ???? to use when I SaveAs prior to emailing as an attachment. My Excel 2003 doesn't recognize the .xlsm file and other formats are eliminated the vba/macro coding

View 4 Replies View Related

Opening 2003 Files From Word 2003

Jan 8, 2010

how to write a code where i can open Excel2003 file from Word2003 and delete data from Sheet1 and then close the excel2003.

All this is done from Word file.

View 9 Replies View Related

Selecting MS Word 2003 Table From 2003

Feb 13, 2010

I can select a MS Word 2003 Table from my Excel 2003 macro? I keep getting Error 438 as per attached file for the line of

View 2 Replies View Related

Countifs But Looking For More Than One Value?

Oct 31, 2013

I am using the formula to count if c- is in the cells: =COUNTIF($B2:$U2,"C-")

However, I need to change the formula so rather than just count if C- is in the cells, I need it to count if A*, A+,A, A-, or B+ are in the cell instead.

View 2 Replies View Related

COUNTIFS Without Duplicates

May 12, 2014

I'm currently working with the formula

=COUNTIFS(Data[Quarter],"Q1",Data[Country],A3,Data[Sales],">2000")

However I need the formula to count only one for the n possible duplicates in the Data[Name] column.

View 8 Replies View Related

Countifs On Two Different Variables

Apr 21, 2009

Say I have a list of part numbers, and each part number has an X or a 0 next to it, depending on my own set parameter.

How do I then report that data on another tab so that it counts how many there are in a set area AND if its an X.

At the moment I have this:

View 7 Replies View Related

Replacement For COUNTIFS

Jun 4, 2009

I am looking for a replacement to the function COUNTIFS that will work with Excel 97-03. I have attached a sample of the document which will hopefully demonstrate what I am trying to achieve. On the sheet labelled 'Data' there is four headings (MerRefs, JCPRefs, Self Refs and Other) for the results beneath these headings I have used the Countifs function. I have attempted to use the SUMIF as an array formula but couldn't get this to work.

Also in the hope of killing two birds with one stone, I created this in Excel 2007 and in the past when I have saved documents as 97-03 documents it used to warn me of any incompatible functions included within the doc and now it doesent. Where can I switch that setting on?

View 10 Replies View Related

COUNTIFS For A Set Of Data?

May 1, 2014

I'm trying to do a COUNTIFS for a set of data. What I want it to do is pull everything that is assigned to a certain person that has no date in one column, and information in another. For example:

Name
Date
Comment

Michelle
4/30/14
Closed

Michelle

Issue

Michelle
4/30/14
Closed

Michelle
4/30/14
Closed
Michelle
Issue

So based on this dataset, I would get the number 2 for Michelle (row 2 and 5 have no date in column B and a comment in column C). I've tried a few variations and can't seem to make it work right.

View 2 Replies View Related

Countifs Function

Aug 24, 2008

I am using the COUNTIFS function. I can us it with simple criterias but unsure to go about this criteria.

I would only like to count the cells if the range in question is equal or greater than S3 but is equal or less than T3.

I don't know how to use the => signs.

View 9 Replies View Related

Multiple Countifs

Nov 11, 2009

I have a list of information regarding jobs that need to be carried out by a number of companies by a number of dates. What I want to count is the number of jobs that company A need to caary out by a specific date and then the number that company B need to carry out by the same date. The formula basically needs to say countif column a equals company A and column C equals 11/11/09. I have tried numerous combinations of IF, Countif, SUMif, And etc but can't get the formula to work,

View 9 Replies View Related

Countifs On X Axis

Nov 26, 2009

I'm looking to count the following:

A B
a 1
a 1
b 0
b 1
c 0
a 1

If column A=a AND column B=1, add column B on the same axis. I can only get a function that adds all B column if A=a. The function should equal 3. Cells B1, B2 and B6 should be the only ones counted.

View 10 Replies View Related

Countifs If SUM Of Range Is Between Two Values?

Mar 27, 2014

I have the following formula: =IF((COUNTIFS(data_sheet!U:U,Monthly_Report!H10))>166,0,COUNTIFS(data_sheet!U:U,Monthly_Report!H10,data_sheet!BN:BN,"Premier"))

This formula counts values on my data sheet based on the specified criteria. I have this broken ito 3 section, each section that needs to be summed.

Section 1 can not be greater than 165, if it is all the values in the range needs to be reset to 0
Section 2 can not be greater than 190, but at the same time can not be less than 166, else is need to be reset to 0. How would I adjust the above mentioned formula to check these criteria?

View 4 Replies View Related

Adding Between Condition After Countifs

May 12, 2014

I'm currently using the following function: =COUNTIFS(Data[Quarter],"Q1",Data[Country],A3,Data[Sales],">2000") which works perfectly. However I'd like to add another condition "between 1000 and 2000".

I've tried this but I'm not getting any luck: =COUNTIFS(Data[Quarter],"Q1",Data[Country],A3,Data[Sales],AND("<1000",">2000")).

View 3 Replies View Related

Excel CountIFS - Adding 1 To The Value

May 27, 2014

=COUNTIFS('Gone Out'!L:L,"=27/05/2014",'Gone Out'!A:A,"=John Smith")

Where I have the date why will this not work?

=COUNTIFS('Gone Out'!M:M,"=E12",'Gone Out'!B:B,"=John Smith")

I have 27/05/2014 that in E12 but it keeps giving 0 instead of the actual number

Also, How would I make it so that instead of adding 1 to the value, i could add John Smith's Age?

View 9 Replies View Related

Absolute Value Inside Countifs?

Jul 9, 2014

So I have a formula which contains a countifs that looks like this:

=COUNTIFS($AZ:$AZ,$AZ15,$AM:$AM,ABS($AM15),$AD:$AD,$AD15)

However, I want the formula to count values when ABS($AM:$AM)=ABS($AM15) but I get an error if I try to put ABS($AM:$AM) as my range. Is there anyway to set my range as the absolute value of Column?

I have also tried ABS($AM15:$AM1000).

View 10 Replies View Related

COUNTIFS With Unique Values

Dec 23, 2013

I am writing a formula to accomplish the goal of counting only unique text values in a column, based upon two different factors:

*Need to Count unique text values in column named 'CASE_NAME'
*Column 'FISCAL' must match cell A5
*Column 'ZONE' must match cell A3

View 5 Replies View Related

Adding A Second Option To COUNTIFS?

Dec 26, 2013

I have a COUNTIFS function that, among other criteria, only counts cells in which the value is "P". I now want to modify this function to count cells in which the value is "LA" in addition to cells in which the value is "P". I'm not sure how to work this into the argument. I tried using OR("P","LA"), but that's not working.

View 3 Replies View Related

COUNTIFS Across Multiple Worksheets

Jan 24, 2014

I am having a problem getting my formula to return a result. The basics are that I would like a count of a specific set of data in a section of a workbook. So in column 'A' I am searching for 'TextA' but this also has to have 'TextB' in column C. The problem is I have 12 consecutive monthly worksheets I would like to pull this data from and I only really want to write one formula. I can (and started to) use the countifs function plus countifs function etc referencing each worksheet individually but this seems long winded.

I have read on this forum that I can use sumproduct combined with countifs but I can't seem to get it right. I have listed my formula for a single sheet countifs function below, but I don't know how or if to include the sumproduct function to reference the rest of the monthly worksheets. The other thing I should mention is that there are other worksheets too, so it would need to be specific to this range, but they are consecutive within the workbook.

View 4 Replies View Related

COUNTIFS With Many Criteria In Same Range

Feb 5, 2014

How to combine many criteria in a COUNTIFS formula with the same range.

I made a formula:

=COUNTIFS(brut!$B:$B;"1T";brut!$G:$G;"F25"; brut!$C:$C;"1")

And it works fine, but I need four more conditions -

brut!$C$C; "2", brut!$C$C; "3", brut!$C$C; "4" and brut!$C$C; "5".

When I change the formula to

=COUNTIFS(brut!$B:$B;"1T";brut!$G:$G;"F25"; brut!$C:$C;"1"; brut!$C$C; "2"; brut!$C$C; "3"; brut!$C$C; "4"; brut!$C$C; "5")

It didn't work anymore.

I don't know how to use IF and COUNTIFS together.

View 4 Replies View Related

Countifs To Count Each Occurrence Of AA Or DC By Itself

Aug 13, 2014

I have a column of data that contains a two letter identifier, which can sometimes be combined with another 2 letter indentifier: Example

Column D
AA
DC
DC,AA

How would I get countifs to count each occurrence of AA or DC by itself?

View 8 Replies View Related

Date Range And COUNTIFS

Dec 28, 2008

I need to look at a series of data once per week and calculate the results. I'm trying to use COUNTIFS to get a result based on both the date I'm doing the analysis for and for the response.

I've attached a sheet that shows a sample of what I'm trying to do. Cell B1 contains the date range I'm using, and cell B18 shows the formula I'm having trouble with. I suspect it's because I'm trying to treat a date like text, but I'm not sure.

View 8 Replies View Related

COUNTIFS With A Variable Condition

Apr 1, 2009

I am using COUNTIFS to work a formula for finding an average of a SUMPRODUCT. Formula:

View 2 Replies View Related

Countifs With References As Criteria

Aug 13, 2009

I'm trying to use this formula in a cell but it keeps giving me #NAME.

What does this mean?

View 8 Replies View Related

Month Criteria In Countifs ????

Feb 11, 2010

check out the COUNTIFS function on Data Tables Sheet -> Cell D29 and pls. let me know what's going on. I need is : To calculate the number of items on system data tab which has the "Pending" status and items which belongs to month "Jan-10" under a particular person selected from B 27 cell (Data tables tab).

I solved it by using >=01-01-10 and <=31-01-10 criteria but i don't think its nice idea .... I tried to use AND(function by got failed ...........

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved