Free Microsoft Excel 2013 Quick Reference

#VALUE! when I use SUMIF

I am referencing data in a separate sheet using SUMIF function. If the separate file is not open, my function results in #VALUE.
Anyone knows what to do, please? I have tried SUMPRODUCT but not successfully.
Thank you

Post your answer or comment

comments powered by Disqus
When I use SUMIF(A1:A23,C3*,D123), I get an error not allowing me to use
the wildcard character. How do I get this function to work inside Excel

When I use SUMIF(A1:A23,C3*,D1:D23), I get an error not allowing me to use
the wildcard character. How do I get this function to work inside Excel

I'm new at this, so bear with me. I'm using vba to navigate around in internet explorer. Basic stuff, really. Opening links and saving files to folders and so on and so forth. I've noticed on one instance that when I use vba to open up a page, the page I open doesn't have all the graphics displayed and the vba stops performing properly after that. When I navigate to this exact same web page manually, all is well. Any ideas out there as to what it is might be going awry?

I'm using a dynamic named range (VendorIndustries) to group header items:

=OFFSET('Features Matrix'!$F$2,0,0,1,COUNTA('Features Matrix'!$F2:$IV2))

When I use the Edit|Goto menu function and enter VendorIndustries, the appropriate named range is selected. However, the dynamic named range formula itself changes, usually to:

=OFFSET('Features Matrix'!$F$2,0,0,1,COUNTA('Features Matrix'!$F65517:$IV65517))

Any ideas on why and/or how to fix this?

I had cutomized page margin setting and when I opened the file in a different
computer the page margin setting has been changed...probably what was default
on the latter computer. For example, the the spreadsheet was fit all in one
page and on the second computer the last row goes to the second page....

How do I keep the same page setting when I use different computer? or what
am I doing wrong? Thanks.

My links in my worksheet become #value! when I insert a row/column. Help!

pls help me on this, somehow when i use formula, to link to existing box with
formula, the result will be 0.

plss help it kind of urgent here...

why does my computer freezes when I use save as in word, or any office

My workshop has a distintict text on top of all printable pages. (Sheet2)
When I use Print Title on the first page to insert a logo for all pages the text which is on the top of every page moves or disappears (you can see on print preview Sheet 2).
Do you have any solution to keep my top dinstinct text on the same spot on every page and use Print Titles to insert my logo or other pictures to all pages.
I'm open to other solutions if you have guys.
Thank you.

When I use conditional formatting in Excel, the font size is grayed out and
the options for the border are limited to 5, none of which are the ones I
need, is there a way to make more formatting options available?

Why is my chart placing one column on top of another when I use a second y

I am trying to utilize a SUMIF function that references a cell to provide the
the criteria, so that I can changed the criteria inside a optimization or
scenario. My functions are all returning zero when I use the cell reference,
but when I use the actual value, it returns what it should? Any way I can
get around this?

I want to create a function to return the gestational age of babies on
their date of birth (DOB) with reference to the due date (the
estimated date of confinement or EDC).

The function I have at the moment is:

Function Gest(DOB As Variant, EDC As Variant) As Single

'Returns the gestation in the form x.y where x is the number of
completed weeks
'and y is the additional days, which can range from 0 to 6.
'The calculation is based on the DOB and the EDC, and assumes a normal
'duration of 280 days, with the start being day 0 (not day 1).

Dim Gestation As Double

Gestation = ((280 - (EDC - DOB)) 7) + (0.1 * ((280 - (EDC - DOB))
Mod 7))

Gest = Gestation

End Function

When I test this within the immediate window I get the result I expect

However, when using the function in a worksheet with the same DOB and
EDC I see a value of 31.3999996185302 (when I use formula auditing).
I had wanted to compare my calculation to the value displayed in an
existing 'gestation' column.

1. What is causing this behaviour?
2. Can I return the function value (as displayed in the immediate
window) to the worksheet cell?

Kind regards,
John McTigue

Hi all,

I have 4 drop down boxes controlling data on two parts of my worksheet.

When i use the first two boxes i get a result on a graph. The second two drop down boxes do the same but on a different graph.

What i would like to be able to do is use a tick box, so that when the box is ticked, it overrides the second two drop down boxes so that the data matches the first two drop down boxes.

By doing this the user has the oppurtunity to use different data on each graph (when not ticked), or when the tick is clicked, keep the data the same - that way it is quicker to get a result (as you dont need to select 4 boxes, just 2) and it also ensures that the same data is used throughout.

I tried this using an IF function so that when the tickbox is activated it overrides the value in the cell link to the drop down box. This works only once because as soon as you use the drop down box it automatically deletes the IF function and overrides with a number and so its game over.

I cant really think of another way to do this.

Is there a way around this - apologies for not explaining very well.



Hi,I am using a list box just to be a bit different as in attached file

Out of the list in column G, i choose name/names and as per the macro i can select this name only in column E as can be seen

It is working fine but the problem wht i face is

for example

keep curson on E13 choose any name it will be displayed
but now when i move my cursor to E14 to choose one more name, it gives a kind of refresh adn flickers for long, y is that problem or does it happen when list used is too lengthy, i dont want to hve this problem
its annoying

similary if i try to delete a name already selected then also it takes a long time to refresh and deletes, please help

IF thers anyother way i can manage resources please do suggest

I have created a sheet that contains about 10 macros, all of them work properly except for 2 of them. When i add a sheet, they lose their values. what is strange is that the macro is still in the cell. for instance if i come back to my sheet that contains the macros after copying a sheet and then i click in the cells that contain the macro. i will see something like =chemicalvalues(B12) if i click in that cell again the value recalculates. what could be causeijng this?

I have a workbook with many worksheets. The first worksheet has cells containing the names of all the other worksheets, like a table of contents. Next to those names, I want to list the number of nonblank rows on the corresponding worksheet. The goal is to see a list of all worksheets, and their current number of rows, when viewing the first worksheet.

The function I created, shown below, returns #VALUE! when I use it as a formula, as in =LastRow(B2), where cell B2 contains the name of a worksheet:

    Dim rng As Range 
    Set rng = Worksheets(""" & WorksheetName & """).Range("A:A") 
    LastRow = Application.WorksheetFunction.CountA(Range(rng)) 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for any suggestions!

I have a fairly simple vlookup that looks up check numbers from another worksheet and returns a value in that array. I have performed this for every month of the year except one and the formula works in all of the other spreadsheets. When I go to the worksheet where the array is and use the "Find" tool, it finds the value. When I return to the worksheet where the vlookup formula is, the formula works for that check number I have run find for. Nothing I have tried has work, format painting from spreadsheets that work, copying formulas over, rewriting formulas. This month has more rows than the prior months by about 2000 or so, with a total of 3800. Any help would be useful. I have attached the formula below:


Where p4 is a simple dollar value I wish to add to the vlook
up (and this works in prior months)

Column A is the the check number in question, in both work books

Thanks in advance

I am using the below formula to TRY to return nothing if F76 or AS76 doesn't havfe value in it (it does have a formula it it).

In trying to determine what the problem is I used,


it returns 1 which is correct, but when I use the below formula it returns #VALUE! ????????

=IF(OR($F76="",$AS76=""),1,MATCH(AU$11&CHAR(127)&$F76,OFFSET('loi2 TRNWGSSN'!$H$1,$AS76-1,0,$AT76-$AS76+1,1),0))+($AS76-1)

I'm trying to replace the first "R" with an "E" in the text "RMR-071201".
The text is in cell A47 of an Excel 2003 worksheet.

When I use the formula =SUBSTITUTE(A47,"R","E",1) the result is #VALUE!.
The substitute function works OK when I leave out the instance_number but it
replaces all instances of the "old Text" rather than just the instance I want.

I get this result on both of my home computers but my Excel 2003 at the
office calculates this properly.

I've downloaded and installed all the Office updates that were recommended.

Does anyone know how to fix this?



I hope this message finds you well. I had a quick question concering
XValues syntax.

I am trying to set the XValues of series 3 in Chart 1 of Sheet1. I am
trying to use the values within cells C33 to C35 for the XValues.

The code I am using is listed as follows:

ActiveSheet.ChartObjects("Chart 1").Activate

ActiveChart.SeriesCollection(3).XValues =
Worksheets("Sheet1").Range(.Cells(33, 3), .Cells(35, 3))

Apparently, there is compile error when I use .Cell in my code. The error
message is: Invalid or unqualified reference. I've checked the syntax
against the VB reference manuals, and it appears correct.

Does anybody have any ideas? Thanks and have a wonderful day!

I am automating an excel spreadsheet by using a userform with several
textboxes and a flexgrid. It works fine, but every so often for
whatever reason, the file size of the excel file increases in a very
large manner. I started with a 43kb file and it was working fine. Then
I noticed my machince was so and when I checked the file size it was
like 33Mb, but if I go back to the original file and redo everything it
is fine again for a while. Does anyone know what causes this? It has
happened in both Windows XP and Windows 2000.


Doug wrote:
> I'd like to have the cell address returned along with a value when I use the
> MAX function. Is there a way to do that?

If the functions in the freely downloadable file at are available to your workbook


filled down to accommodate the number of occurrences of the maximum value.

Alan Beban

I'd like to have the cell address returned along with a value when I use the
MAX function. Is there a way to do that?

No luck finding an answer? You could always try Google.