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
ADVERTISEMENT
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
Jan 26, 2012
I know you can't use the SUMIFS function in 2003 but give alternative in 2003
=SUMIFS($D$3:$D$30000,$C$3:$C$30000,"IOP",$E$3:$E$30000,">=01/03/2011",,"
View 3 Replies
View Related
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
View Related
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
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
Nov 28, 2012
Link: Create a workbook from every worksheet in your workbook
It works great, but is it possible to modify this to export each worksheet as an html file instead of an Excel workbook?
I'm using Excel 2003.
View 2 Replies
View Related
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
Aug 16, 2014
Intend creating a calculator that shows me how much a certain hotel room costs at different times (seasons) of the year, for example I have a One Bedroom Budget Apartment (list of room types is extensive), I also have three seasons, low, S Hols and Xmas, so I have a sheet created and inserted drop down boxes for the RoomType, Season, and Number of days, I now have to insert a formula that looks for the three variables and inserts the cost (I already have this info on a worksheet)
TotalCost
RoomTypes
Season
Days
[Code].....
Also I am using Excel 2003 so no combo box option (that I can find anyway)
View 1 Replies
View Related
Sep 28, 2008
Row totals exceed the limit for Excel 2003, so I have split the data into different worksheets by year.
Problem is, now I can't create a useful pivot table, i.e. using 'multiple consolidation ranges' reduces the field list to "Row", "Column", and "Value".
I have 26 columns in each worksheet (all identical structures); some are multilevel factors, some are variables - and the way these relate to each other is unknown as this stage, so I literally cannot have the dimensions reduced in the way Excel proposes with multiple consolidation ranges.
View 7 Replies
View Related
Jan 6, 2009
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
View 9 Replies
View Related
Dec 2, 2011
I have a sheet that column G and column H has duplicate values. I would like to be able to put a formula in a cell P4 that will search thru column G and column H and if there are any matching items then add the corrisponding number in column I.
So say cell G8, G25, and G30 have the same and H8 and H30 have the same value, i would like this to add the values of cell I8 and I30.
Windows XP
Excel 2003
View 9 Replies
View Related
Jun 30, 2009
Does the following formula make sense and if not, what do I need to change? I'm receiving a "#VALUE" error. =MROUND(IF(A9=1,SUM(D1*0.1), 0) (IF(A9=2, SUM(D1*0.1/2), 0)), (IF(A9=3, SUM(D1*0.1/3), 0)), (IF(A9=4, SUM(D1*0.1/4), 0)),6)
It is possible to have multiple IF,THEN statements in one formula? A9 and D1 are the variables driving the formula. Let me know if I need to provide a detailed explanation.
View 4 Replies
View Related
Aug 23, 2006
I have are two listboxes set up, one with all the states, the other with 7 possible variable selections. So in my case I have 371 (DC, Virgin Islands and Puerto Rico are included as states in this) possible combinations here. With each combo the user needs to be able to enter up to 2 years of quarterly data (8 quarters, so 2968 pieces of data possible) and then press a button to save that data to a specified location which I will need to access at run of the overall macro.
Is there an easier way to do this then to simply assign each combination a cell? Maybe an If State = AK and Variable = TUR then find column = AK & row = TUR (per quarter) type solution?
View 6 Replies
View Related
May 23, 2007
I have just now dutifully searched here, and elsewhere, on the topic of subforms in Excel VBA, and find nothing. Am I correct in assuming that one simply creates several userforms (and perhaps makes some modal), and launches one form from another? Are there any tutorials on guidelines for using several userforms at once?
View 6 Replies
View Related
Jul 2, 2014
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
View 8 Replies
View Related
Jun 24, 2012
I need to calculate the average spend on a day of the week over the month, so all Monday's or all Tuesday's, etc. One sheet is one week so I need to average b16 on 6 sheets as an example.
I used =AVERAGE('WEEK1:WEEK6'!B18) to calculate average over the six Monday's. The issue is, as in other posts, how do I ignore the cells that have a zero or null value.
I've tried adjusting this which was in 1 post
=AVERAGE(IF($C$2:$CA$2=C62,IF($C$25:$CA$25"",$C$25:$CA$25
with this
=AVERAGE(IF(1+1=2,IF('WEEK1:WEEK6 '!B180,'WEEK1:WEEK6 '!B18))) which returns #REF!
this from another post
=SUM('WEEK1:WEEK7 '!B18)/COUNTIF('WEEK1:WEEK7 '!B18,"0") which returns #VALUE!
and this
=AVERAGE(IF('WEEK1:WEEK7 '!B180,'WEEK1:WEEK7 '!B18)) which returns #NAME?
The cells on each sheet are sum formulas for other cells on the sheet not just numbers on their own.
Using windows 7, excel 2003
View 3 Replies
View Related
Jan 31, 2014
1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.
2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)
3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .
View 1 Replies
View Related
Jul 22, 2009
I have a workbook in Excel 2007 where I am creating a summation worksheet through the use of arrays which sumif and countif the other worksheets. Things went fine until about halfway through when I started getting merged cell errors. In between the calculations, I had some merged cell rows for section titles. I was not entering the array into them, but researched the error and unprotected the cells. Problem solved. Now, with 10 row to go, I started getting the error again. I unmerged the sectional title rows. I verified that the cells are not protected. However, when I ctrl+shift+enter to input the array, my formula is moving from the cell that I am inputting to, to the cell in the row directly beneath it.
View 3 Replies
View Related
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
Jan 4, 2012
i have 10 sheets in my workbook and i wanted to do countifs on every sheet for column a so i have created 2 more sheet one called start the other call end and moved them start before sheet1 and end after sheet 10
this is the formula i was trying to use =COUNTIFS(Start!:End!A:A,finished!A3)
View 6 Replies
View Related
Jan 23, 2014
I am currently trying to create a Formula which will count my data on a range of conditions.
Here is an example of my data:
A
B
C
1
Active
4 - High
3 - Medium
[Code] .....
This data then continues on for a number of rows. Basically I want to count the cells which contain "Active" or "Tolerated" in row A which also has a score of 4 or 3 in column B and also a score of column 3 or 2 in column C. So the formula should count Rows 1 and 4 in the above scenario.
The problem I have is the data in B and C contains text as well as a number so I assume there is not a way to use a greater or lesser than operater? and instead I will need to add a condition to just search the cell which contains the number 4 or 3 in column B for example.
I have had a go at this but am not getting very far. Would COUNTIFS be the best formula to use for this?
View 9 Replies
View Related
May 20, 2014
I would like to count the number of values in column B IF the value in Column A is equal to a value from a range of cells.
I would like the formula to be in cell E7. And the range of cells containing the values is in column D.
Here is my workbook Book1.xlsx
View 1 Replies
View Related
Oct 23, 2013
This is the function I have now: =COUNTIFS(Sheet1!A:A,Sheet2!A2,(Sheet1!E:E),"*") and it works great, counting every cell with a value in column E.
When I make criteria range 3 another column (criteria also being"*") my numbers actually start going down. I think it might be averaging the two columns out or something but I just want it to count all the cells with values in it from multiple columns and give me a total count.
I have 4 columns total that I want to be able to get a total count from.
View 3 Replies
View Related
Jul 8, 2009
I want to count the number of occurences involving 3 columns of a spreadsheet, but one of the columns is a date/time field, and I only want to analyse the date.
Example:
A B C
John 1 02/07/2009 09:45:10
Peter 1 03/06/2009 10:14:12
Fred 1
Peter
John 1 02/07/2009 09:45:10
How can I ask how many occurences of John are in column A, with a 1 in column B, with the date 02/07/2009?
View 9 Replies
View Related
Aug 22, 2007
I am trying to create a simple user interface type thing so that someone is able to select from drop down lists someones information, such as whether they are male or female, aged between 19-35 or 35-67, whether they are studying in a business area, legal or construction etc (there are 6 variables in total), This will then give the probability of success of the person passing this course based on probabilities which I have already worked out. I have worked out how to do the first stage of creating a drop down list showing alternative choices with Sex, Age etc in the data validation options, however:
There are 517 possible combinations, as in Male aged 19 to 35 studying Business (with other variables) or Male aged 19 to 35 studying Law (+ other variables) etc etc etc each with their own probability of success. Due to the long nature of writing out Male1935BusinessNorthWestWhiteBritishCollegeBrown I have rewritten it so it appears in the excel file as M1935BNWWBCB, which obviously wouldn't make any sense to someone if they had to select M 1935 B NW WB CB from drop down lists.
Along side the M1935BNWWBCB there is the probability of success specific to that type of person. So for example I could would have:
M 60%
M1935 64%
M1935B 35%....
View 8 Replies
View Related
Apr 14, 2014
I have multiple rows of data per each column. I'm trying to count rows that match a value that ISN'T any of the expected values in column A as well as a certain value in column G. This is in case future data is introduced that doesn't match the existing codes.
I'm trying to count all rows that DON'T have a value in column A beginning with PUA or P9V, so far I am trying this:
=COUNTIFS(A:A,"<>P9V*""<>PUA*",G:G,"P")
But it only seems to work on the "<>P9V" part. When I put P's in the column G of which the column A matches PUA*, it still counts the cell. I've tried a couple of AND variations but none are working.
View 2 Replies
View Related
Feb 26, 2014
Need to use CountIFs formula to evaluate multiple conditions in the same column. Here is the formula I tried. It works for Resolved but returns 0 when i try to add Duplicate.
=COUNTIFS('P12 Source'!H:H,A18,'P12 Source'!F:F,"Resolved,duplicate")
Also used this formula with success by referencing a cell that had "Resolved" in it but when I tried to add another cell with "Duplicate" it again returned 0.
=COUNTIFS('P12 Source'!$H:$H,A19,'P12 Source'!$F:$F,'P12 Source'!$F$75)
View 8 Replies
View Related
Oct 10, 2009
I am unable to get the correct results using Sumifs and Countifs function. Below is an example:
If I use the formula as =IF(ISERROR(SUMIFS(F2:F8,B2:B8,"Africa",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"2B",E2:E8,"SY")/F9),"N/A",SUMIFS(F2:F8,B2:B8,"Africa",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"2B",E2:E8,"SY")/F9)
I get the result as 0 but the result i want is 19.67% (1234 /6275). Since i have multiple criterias which might not be available at times in the data, i get the value as 0.
BCDEF2CountryMain Conseq.Other conseq.Product Type Amount 3AustriaInternalNoneInt2B $ 278 4AustriaExternalNoneExt3C $ 900 5AustriaExternal-8A $ 2,388 6NorwayInternal-MV $ 567 7AfricaExternal-SY $ 1,234 8AfricaInternalNoneIntJN $ 908 9TOTAL $ 6,275
Similarly i have used a formula using Countifs; =IF(iserror(countifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=3C")+counifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=2B")/F9),"N/A",countifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=3C")+counifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=2B")/F9)
And even the above formula does not work with the criterias given. What i require is, if the country is "Austria", "External", and "NoneExt" then count those lines which has product Type as "3C", 2B". So the result for the above should be 1.
Request you to kindly let me know the correct way or the right formula to extract data for the above criterias.
View 9 Replies
View Related
Dec 20, 2006
I have a control box button on a sheet and when I click it, I'd like to take the average for the values in Cell M8 on all Worksheets in my Workbook with the exception of the ones named Cover, Opportunity Map, and Average....and put the average calculated in cell M8 on the sheet called Average.
My issue is that the number of worksheets may change for each instance, which is why I'm trying to incorporate any that might be in the workbook other than a few named ones.
Is this possible and if so can anyone assist with the code?
View 9 Replies
View Related
