returning

=SUMPRODUCT(--(YEAR(B2:B589)=2017)) =SUMPRODUCT(--(YEAR(B2:B589)=2018)) =SUMPRODUCT(--(YEAR(B2:B589)=2019)) I am using the code above to count based on a date in a continually expanding table. If i expand the range beyond B589 it returns #VALUE regardless of data that is in the table and I'm...

Hi, I have an equation below that should return a 1 if true (which is working) and a 0 otherwise for any other values (which is working partially). So for some items it is returning 0 but for others it is returning a FALSE which should be a 0 if the equation was working correctly...

Hi, I'm new to VBA and don't quite really understand much, I apologize, I'm trying to do a Vlookup from another workbook. All I want to do is in cell b9 in my current workbook, search for values in another workbook (Finance Extract.xls) from cells d9 to last cell and return a value Here is the...

Howdy, I am working with the following formula in my spreadsheet. =IF(ISBLANK(F3),"Never Reported",IF(OR(MONTH(TODAY())<MONTH(F3),YEAR(TODAY())<YEAR(F3)),"Not Reporting","Reporting")) F3 has a formula in that cell as well which reports either a date or blank. When a date is in the cell the...

Hi, I have the following numbers in range AS133:BD133 ... <tbody> 10 23 2 2 23 97 58 23 9 54 78 23 </tbody> I would like to return the first 6 unique numbers from that list into the range BF133:BK133 I'm currently using the following formula in BF133 and dragging across ...

Hi, I was looking to see if anyone knew a way to return a table of information by selecting single value from a drop down list. My issue is that the values in the table will be different with each single value so I am not sure how vlookup could work with that. Think of selecting one value from...

I'm hoping this is an easy fix. I need a formula that will average data in a column if a column contains x or y. Currently I have: N22 ={IFERROR(AVERAGE(IF((SEN={"n",""}),KS2A)),"")} However, this is returning 4.49 when it should be 4.83. It might be converting blanks to 0 hence the...

Hi. I have an issue with cell DU5. All other cells work properly except this one. You can see that I am trying to return the best result out of 10 possible ones. It seems to work for the other 2 columns next to it, but for this cell it is returning 6.00%, when it should be returning 8.00%. I...

Hi: I want Excel to return 0 if value in A2 is equal to 1310 or 1425 or 6756, if not return value in F2. This formula is returning error #value. IF(OR(A2=1310, A2=1425, A2=6756,””),F2) Could you help please? Regards, Sean

Hi, I have an index match formula returning the value from a range based on a criteria for Row 1 and another criteria for Column A: =INDEX($B$2:$AH$5,MATCH($AL1,$B$1:$AH$1),MATCH($AK$1,$A$2:$A$5,0)) However this is returning a #ref ! error. HOWEVER, if I reduce the number of columns in the...

Hello: IFERROR(INDEX($E$3:$E$39,MATCH(B50,$B$3:$B$39,0)),"")*-1 This formula above is returning #VALUE ! if value in B50, is not found in range $B$3:$B$39 When I remove *-1, formula is returning "" but I need to have *-1 in formula. Can someone adjust formula so it returns "" when *-1...

I am receiving "find" method results that are inaccurate. I am using date values of the type MM/DD/YY and searching in another worksheet in the same workbook with date values like MM/DD/YY. Most of the date values are returning values as you would expect, except for values beginning with "02"...

Hello, I have an intermittent problem with an RTD add-in that provides live stock/options quotes into excel. The add-in has been provided by the company ("webiress"). I am requesting about 270 quotes at a time (or might be double this if (ask + bid)/2 in one formula is counted as two quotes)...

Hi All, I am trying to get the following formula to work, but its returning a zero. =COUNTIFS(D3:D2713,"Red",$AB$3:$AB$2713,">=-2.50", $AB$3:$AB$2713,"<=-0.99") I want the formula to count the amount of cases that fall in between the costing criteria listed below? <tbody> Charge Criteria...

Not sure what formula to use. Currently I am using index match but whilst it is returning a result it is not quite returning what I want. I have a table which contains the following info: <tbody> Staff Reg 1 Reg 2 Reg 3 Reg 4 AAB 7.1/Rg SAH 7LA/Rg 7LB/RG 7.1/Rg MAH 9.7/Rg 9BW/RG...

Hi: Could you help me solve this please? Column G has values like API0001521 or EE0001625 L2 has formula =IF(G2="API*","",INDEX and Match formula))) Excel is returning #N/A and not "" if G2 has value API... INDEX and Match is returning required value if value in G2 is not API Could you help...

A3=IF(FIND("-",A9),LEFT(A9,FIND("-",A9)-1),CONCATENATE(A9)) returning "#VALUE!" instead What I am trying to accomplish is that if I typed/pasted ABCDEFG1-23 into cell A9, cell A3 then will display ABCDEFG1 and if I typed/pasted ABCDEFG1234 cell A9 would then equal the whole text in a cell A3

Hi, I am having difficulty in hiding a "0" if nothing is found using the following formula =IFERROR(LOOKUP(2,1/($AZ$13:$AZ$5000=D12)/($AS$13:$AS$5000=F12),($AX$13:$AX$5000)),"") is there a way for the formula to return "" instead of "0" Many thanks

Hello, could someone please help where and how I would place the IF formula to remove the #DIV-0! error im using =(O18-H18)/ABS(H18) but there is on value in cell H18 so it returning an error Many Thanks Steve

Hello All, I am using the formula below to return a value based on what is entered into the cell next to it. I am running into a problem where it is returning the value of 150 if the field is blank. I know this is happening because it is looking for anything less than 625. I want the filed...