I attached herewith a file filled with Countif, sum, or, and. I look for a VBA to get lighter file. Because the file in fact will be filled up to 40,000 rows.
View 7 RepliesI wonder if there is a way to enter my formulas as an array formula using Control-Shift-Enter in a more time saving way than one line by one line
I have 600 rows of array formulas all in column E but at different intervals with some blank rows inbetween
The formulas are in place but just need the CSE to enable the array formula { }
I am not looking forward to line by line entry.
I have these light blue lines showing up on my sheet. I have "not boarders" selected and they do not print - but I can see them on the screen. Does anyone know how I can get rid of them?
View 3 Replies View RelatedI want to include a simple dashboard-light, which turns green, yellow or red, dependent on a value, eg in A1. [like the one appearing after my name]
A1<100 => light turns green
A1 100-500 => light turns yellow
A1 >500 => light turns red
Does something like this exist? Preferably in VBA code?
How to light up a row in excel when working in that row?
I am seeking to insert an image into an Excel cell by a formula or a macro.
I am currently trying to have a green traffic light appear in a cell if another particular cell has the numerical value of "1".
for example, something along the lines of: =if(A1=1,"image1.jpg","").
Now, i know the above formula doesn't work but is something similar possible?
Is there a way to light up a row when a cell in that row is selected?
I'm working within columns A through H.
I need to sum the cells that are light green in color. The color is manually added. I guess I need a macro for this. I have never done a macro so be very specific.
View 5 Replies View RelatedI'm trying to create a dashboard with traffic lights displaying the status of a task. My manager wants a to be able to pick a color (Red, Yellow, Green) from a drop down menu and then have that be displayed as a red, yellow or green traffic light. Using the conditional formatting option, I'm only able to use numerical values or a formula. I'm hoping there's a way to either create a drop down list with the 3 different colored traffic lights or a way to use VBA to have the conditional formatting search by text instead of numbers.
View 2 Replies View RelatedIF the list value in D2 is = to MT,GP,or H then I need the entire row 2 to turn light gray.
View 6 Replies View RelatedI have a spreadsheet that contains a list of dates: What I need is a formula that will count the number of cells that hava a date more than 6 months old. I also would like the field automated so I don't have to change the date manually every day.
=COUNTIF(S5:S593,"<2012/06/18") This formula will give the correct read out, but I must change the date manually.
My attempts at adding a =today() command in place of the date result in 0 being the result (not correct)
This is what I have tried: =COUNTIF(S5:S593,"<"=TODAY()-"183")
I am wanting to have conditional formats so the cell fill in a range on sheet 1 will change to:
- red if the value of a cell on a seperate sheet (date) is greater than 18 months old
- yellow if the value of a cell on a seperate sheet is 'In progress'
- green if the value of a cell on a seperate sheet (date) is less than 18 months old
What I need to do it create a traffic light icon, just the ones in Excel based on the dates within the field. If there is a date present which is past todays date then I would like it to display the green traffic light icon, if the date present is scheduled for a future date I would like the amber traffic light to show, and finally if there is no date present at all I would like it to display a red traffic light icon.
View 1 Replies View RelatedUsing 2003, I'm trying to do the following, data starting in A3, and ending in L3:-
- If A3 is blank, cells have no format
- If A3 has a value, with A4 blank, fill is light blue and bottom line solid, top line dotted
- If A3 has a value, and A4 does too, fill is light blue, top and bottom line dotted
- If H3, which is a date field, is greater than today font is black, if less than today, font is red
my current formula is =COUNTIF('Input Page'!A2:A50000,"=Monday")
i'd like to change it to check what day is in the field and then only do the above formula if that day is within the past week.
so i need the "=Monday" section to be changed to read "(is equal to monday) and (is between today and today-6)" ...todays date will be taken from 'Input Page'!B2:B50000
See attached document, there are 11 cells in which will either contain Yes or No. Looking at the different combinations that there can be there can only ever be 9 out of the 11 cells being used or 10 out of 11 being used.
Also the last question (Row 25) could be filled N/A if this occurs I would like the formula not to count that. Is there a counting formula or IF formula which can be done to help me out?
I'm trying to work out to most efficient way of making a quality monitoring system which uses traffic lights to show problem products.
The traffic light system need to obey the following "rules".
Each product is tested once daily When a product fails once it moves to Amber, When a product Fails twice in a row it moves to RedOne pass when a product is in Amber Status, will reset it to green.If a Product is in Red status, it much have 3 consecutive passes to reset to Green.
I have tried to use array formulae to look up a column for previous values in the past for this sort of thing, but with 100000+ rows it runs far too slowly.
Banana Problem?
Product
Result
Traffic Light (0=green, 1=amber, 2=red)
[Code].....
i have a spreadsheet that acquires data from 42 other worksheet and in a row of cells it has yes or no at the bottom of that row i have =COUNTIF(A2:A34,"YES"). But it will not count the yes' because the cells that it is trying to count all have formulas in them like =tues!A34 or similar is there a different formula i can use to still count the yes' even in a cell containing a formula?
View 7 Replies View RelatedI already have a conditional format which is working OK. I would like to put a formula in the cell so it shows a % so currently it would be 100% as there is 3 cells below showing 3 greens.
Alternativily each colour is worth a value - Green = 1, Amber = 2, Red = 3. So Cell I3 would show 3.
I need to add more arguments to a countif formula, but I have maxed out the 1024 character limit.
Is there another way of writing this formula or is there a way I can add more arguments to this formula.
Formula is:
=countif(A2:A9999,"AH")+countif(A2:A9999,"ARHC")+countif...
I have a list of the arguments, can I do something like:
=countif(A2:A9999,B2:B50)
(with the list of arguments in columns B2 to B50)
is there any way to put 2 criteria into a countif() statment?
Say A1 to A100 are filled with information
I'd like to count if things are equal to 4 and 5
I know this isn't proper syntax:
=countif(A1:A100, "4" && "5")
I'm trying to get something like that to work.
I want to count the number of cells in column B that contain the start with the string US/IL. Here's the formula that I created, but it returns a value of 0 instead of 590.
Is there something I need to do differently when a cell value contains the / character in the string?
=SUM(COUNTIF(B5:B1830,"US/IL"))
Once I have the correct formula for the above, I want to write another formula on the row below that counts how many rows of this 590 value have a corresponding "Yes" value in Column M.
The first formula will sit in cell B1840
The second formula will sit in cell B1841
I am using the following formula to calculate the number of responses:
=COUNTIF([Cleanliness of Environment:],"None of the above")
However, I only want the number of cells which contain words other than "None of the above". The formula is counting empty cells. Is there a way to weed out the empty cells and receive a tally of only cells with words other than "None of the above" in them?
It's been awhile since I've used excel formulas so I'm very rusty but I need to count the number of times I see a particular item in a list as long as it has another criteria as well. Example List and Result are below: I want to count the number of AAA Rewards that Alex has....
List
#DateEmployeeAmountReward Type
14/17/07Alex$5.00AAA Reward
24/19/07Joe $10.00AAA Reward
34/19/07Alex $5.00 Store Certificate
44/20/07Alex $10.00 AAA Reward
Result
Employee # AAA Reward Amount # Store Certificates Amount
Alex
Brian
Joe
I want to count all the cells in a range that visibly contain no data, so either have no formula or are =""
I tried both of the following with no success
=COUNTIF(A1:A9,"""")
=COUNTIF(A1:A9,"""""")
The goal is to count up the number of rows in a database that have both properties:
1.) empty cell in Column B
2.) "XYZ" found somewhere in column D
I am trying to use a countif and formula but can't get it to work. This part of the formula works fine =COUNTIF(J:J,"
View 9 Replies View RelatedLets say I have 30 rows of numbers in two columns. I know I can multiply each number together in each row with an array formula ={A1:A30*B1:B30}
Question:
Given the same ranges, except no numbers but just the text 'x'. some rows have 1 x some have 2 some have none.
Can I use 'Countif(A1:B1,"x")' in an array formula and if so how would that be structured?
how do i change the below COUNTIF formula so that the criteria is >0 but
View 9 Replies View RelatedI have a sheet with repeated dates for several months and I need to break out data by week and then by certain criteria. I can do 1 or the other but combining the COUNTIF formula and the SUMPRODUCT formula has proven to be beyond me.
I have this now:
=SUMPRODUCT(--(D2:D31719"7/19/2008"+0),--(G2:G31719>"5"))
but it returns a value of 0 which is incorrect.
What I need to do is have the formula return a sum of all of the fields in colG that are >5 within a date range. Once I find that # I have to divide it by another field and multiply by 100 to get the percent.