How To Use A SUM Array Formula That Ignores Letters

Feb 15, 2014

I have two array formula's, one to count hours, the other to count days. Both are based on: type of worker, day of week, and week number. The problem is if I use a "V" or "T" for vacation or training, then the array formula will not work. Currently I leave the field blank if they are not working, but it would be nice to see whether they are on vacation or training. Is there a way i can have my cake and eat it too?

Counting Days
{=SUM(($E$5:$MN$50>0)*($B$5:$B$50=$D56)*($E$3:$MN$3=F$54)*($E$2:$MN$2=$E$67))}

Here is a breakdown of what each of the ranges mean:

$E$5:$MN$50 is the area which the hours are forecasted$B$5:$B$50 is the column for which type of worker (Electrician, Pipefitter, etc)$D56 is the specific type of worker we are counting for$E$3:$MN$3 is a row with all the week days ("Fri, Sat, Sun...)F$54 is the specific day we are counting for$E$2:$MN$2 is a row with all the week numbers$E$67 is the specific week number we are counting for

I am trying to setup a formula that will add 20 days to date entered in another cell. If the date entered is the 31st (ie 5/31/2014) I need it to return the 21st of the next month. Using the example date - 5/31/2014 - it would return the date as 6/21/2014. I would also need it to return the 30th day if you enter the 11th - ie 6/11/2014 would return 6/30 not 7/1.

I have two, technically unique text values, with leading zeros, countif is including them in the count for each.

D17 = 01234 D18 = 1234

formula in column "C" =IF(D17<>"",COUNTIF('Part Numbers'!B:B,D17),"") =IF(D18<>"",COUNTIF('Part Numbers'!B:B,D18),"")

both countif's are resulting in 2, whereas they should be 1 since they are textually unique. The cells themselves, on both the source and counting side are formatted as text.

I need to average the figures in several cells. However some cells have a 0 in them.

I therefore want the formula to ignore the cells which have a zero.

I have used the AVERAGE & AVERAGEA function, but both count 0 cells. (although AVERAGEA ignores blank cells, I need to keep the 0s in as they are linked to another formula)

Pretty straightforward: randomly and infrequently, Excel ignores my selection of the rear tray paper source on my MX870 Canon. Able to correct issue only by closing out and re-opening Excel.

range formula that i am using for drop-down lists. One of the drop down lists is based on the range where the data changes(it is a list of jobs that has been released from cad room and when they have been machined a "yes" in one of column appears and then it needs to be filtered so that only non-machined jobs are visible).

Bitmap Part Description

Expected Spindle Hours Clock In VLOOKUP

[Code]....

I need a formula that is not taking into account values from hiden rows. OR the formula that is not taking into account values from the rows that have Yes in the 4th column.

I have a worksheet which has various figures for each day of the week however I need to establish the weekly average of these figures.

Due to the way in which the figures are displayed, I am unsure how to use a formula which does not require a range with cells located adjacent to one another.

I have attached a test sheet as an example. The cells in yellow require the formula and I need a weekly average for criteria 1-3. This formula also needs to be compatible in Excel 2003

I am a school teacher trying to adjust my tracking sheet to calculate pupils levels. I am looking for 2 potential formulas that will do the following.

1 - In cell AE I would like to generate a formula that will take the data entered in cells J:5, L:5, N:5, P:5, R:5, T:5, V:5, X:5, Z:5, AB:5 and AD:5 and give an average level.

2 - In cell AH is it possible to generate a formula that will calculate how many levels of progress the pupils are making - In other words I need Cell I to be calculated against cell J to see how much progress the pupils are making - for example if in cell I:5, a pupil is was given a 3a, and then in cell J:5 is given a 4b, they will have made 2 sub levels of progress. As well as this, can that progress then be averaged out across cells I:5, K:5, L:5, M:5, O:5, Q:5, S:5, U:5, W:5, Y:5, AA:5 and AC:5 to give an overall number of of levels of progress? An then..... can I colour co-ordinate the cell so that if the pupils are making 3 or more sub levels of progress it turns green, 2 sub levels orange and 1 sub level red?

Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.

Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?

Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.

And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.

I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.

formula to add letters but with a numeric value. this is for a schdule sheet. where w would equal 7.5 and x would be 0.

i am using this =SUMPRODUCT(--(ISTEXT(B3:H3)))*7.5 reads the w and adds up ok but need to be able to put w for work and x for off days and still add the total hours

1. NOT Between 8 and 20 characters OR 2. NOT contain at least 2 numbers OR 3. NOT contain at least 2 letters OR 4. contain characters (e.g. punctuation) which are neither numbers or letters

I have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'

E.g.

frt 34.2 36

p34.5

In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.

I have been trying to create a formula that will save me DAYS of messing around at work.

What I am trying to achieve is to have a sequence of numbers as follows:

BNA01A01 to BNA01A09 then have it change to BNA01B01 to BNA01B09.

This needs to be repeated for all letters to BNA01I09.

Then this sequence needs to be repeated to BNA12.

The last thing is for me to be able to change the formula in order to implement the same sequence on a separate sheet for BNB01A01 - BNB12I09 to BNL01A01 - BNL12I09

In Column A I have first names, In column B I have last Names, in column C I have id letters, Column D another list of First Names And In Column E I have another list of Last Names

So what I need to do in F2 is Look at the name Last name in E2 (Lets say its Smith) then look down the Last names In Column B when you find a match look at the First name on the same row to see if the first 3 letters are the same as the first 3 letters in D2 if they are then put the id that's in cell C into F2 if not ""

I've been trying for hours but no luck, also if you do manage to do it can you tell me how you get it to look at the first 3 letters and how I could change that to 4?

I'm putting together an Environment Threat Assessment for work and want to automate as much of it as I can to alleviate the amount of time ppl have to spend putting values in. My main problem is I have three very important columns: Likelihood, Success Rating and Risk Rating. Likelihood and Success Rating values will be selected from the following:

VL - Very Low L - Low M - Medium H - High VH - Very High Each of these is given a value: VL = 1, L = 2, M = 3, H = 4, VH = 5. The Risk Rating is the result of multiplying the Likelihood and Success Rating. So a Likelihood of Medium and a Success Rating of Low will give a Risk Rating of 6. Therefore, the cells on my spreadsheet would look like this:

Likelihood Success Rating Risk Rating M L 6 The issue is I want a couple of things to occur. Firstly I want keep the values in my Likelihood and Success Rating cells as letters (ie. VL, H, VH etc) rather than numbers, while still generating a number result in the Risk Rating cells. Secondly I'd like to be able to change the colour of the Risk Rating cell to reflect the number it gets assigned. For example Green for < 8, Amber for 9 – 14 and Red for 15 – 25. I've been trying to do this with array formulas and am basically going around in circles with no success.

I am using the formula: LOOKUP(A1,{"S","M","L","XL"},{"S","M","L","XL"}) on a cell containing: EF-2147.(S) so that it will return a S [or M, L etc. if it were .(M),.(L)] but it just keeps returning #N/A. I tried putting in "(S)" etc. in the lookup formula but it then started returning the wrong letters.

I have a question with picking out certain word in the column. I am trying to write a formula that would allow me to pick out names that has first 3 alphabetical letters from the columns. The column has a lot of different names, but the one that I would want my automation to pick out is similar to this, C, AR, AA-103, BG-2056, HJE-1100, etc. However, in the same column, I have other words like elevtar, hsvte, lvnrm, etc. that I do not want my formula to pick out.

Is there a VBA command to get the letters, instead of the numbers, of the column of a selected cell?

I have to letter a list whihc means setting up a loop using character codes.

I may have to go into double letters so I am working on how I would set up the loop for if and when it gets past 90 and starts on double letters. so far the highest is the letter "U"

of course the easiest would be to pick up a column value as a letter

i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it in writing so that i accurately describe my problem. attached is my example

I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?