Sumif returning incorrect value
Operating System: Mac OS X 10.5 (Leopard)
I have data in columns c to f and rows 2 to 20 <br>
I also have data in a2 to a20. <br>
I am trying to sum the data in s2 to f20 when the row matches my criteria in a2 to a 20. The formula that I am using is <br><br>sumif(a2:a20,"=12",c2:f20) <br><br>the result will only sum the values in column c. <br><br>Does anyone know what the problem is? <br><br>Thanks <br><br>Allan
SUMIF() has various limitations.
this will work,...Custom formatting #7
How can I do the following custom formatting –
I need trailing dots before the number
Thanks for any help
Right Click > Format Cells > Select Custom
Enter "........ "0.00
"John Knoke" <JohnKnoke@discussions.microsoft.com> wrote in message
> How can I do the following custom formatting -
> I need trailing dots before the number
> Thanks for any help
Use a custom number format of *.#,###.00
Note the period between the * and the f...Convert time stored as decimalised number to time format
How can I convert 3.5 hours to 3:30:00?
A1/24 and formate as time
> How can I convert 3.5 hours to 3:30:00?
...Keeping the format but update the data
I have a report I run everyday. I want to update the data, but keep the
column sizes and formatting. Any suggestions? Anybody suggest a good ebook to
become more formuliar with excel?
JoeM, this could be as simple as recording a macro during the formatting of
the sheet after importing the data once. Then each day, after importing the
new data, you simply run that macro.
As for online eBook's for Excel, I'm not actually aware of any although I
suspect a Google search would turn up several. It's hard to beat the "For
Dummies..." series of hardcopy...NEED HELP-wrong formatting saved ??
After using word 2007 and saving a word file, it changed the formatting from
the previous file (i think it was .wps microsoft works word processor) and
now its all messed up.....I think I saved it in some other wrong encoding
standard......now, whenever I open this file now half of it is in some weird
looking unreadable characters....like this
is what seems to happen once the system breaks its laws from the inside.
......couldnt find online solutions...does anyone have any suggestions or
s...Conditional Formating: linking to display another cell
Hello, I am trying to make a traffic light with symbols and I've read to 'use
a separate cell for the dropdown choices, with their resulting value linked
into the formatted cell through an IF function, using the character that you
=IF someone enters '1' in B8, THEN display contents of $C$4 (will it display
font and attributes?)
=IF someone enters '2' in B8, THEN display contents of $C$5
=IF someone enters '3' in B8, THEN display contents of $C$6
But I've been reading everywhere and CF is very new to me and I need ...Copying values from place to place
Is it possible say, to type a value in a textbox in one form (Form A),
automatically copy that value, then open another form( Form B) and
automatically paste that value into another textbox in Form B? Is this
Thank you for your help.
If you open Form B from Form A you can use the OpenArgs of the OpenForm
command line to pass a value to Form B.
On the OnLoad event of the Form B you can use set the value in the text box
If not IsNull(Me.OpenArgs) Then
Me.TextBox = Me.OpenArgs
...Infopath w/ manually entered values in drop-down and qry results
I had originally posted this elsewhere, but was told this forum is the
appropariate plase. I have an Infopath form with a drop-down listbox, that
is poulated with manually-entered values.
I choose a value, submit the updated data, and it does put the correct value
in the SQL 2005 database.
However, the next time I query the data, the value in the drop-down list box
is the default value
for the list-box, not the value from the database, which is misleading.
I would have expected the drop-down listbox to display the value from the
database instead. Thanks.
On Tue, 4 Sep 2007, in...Newbie Needs Help With Formatting E-Mails
Newbie Needs Help With Formatting E-Mails
I'm new to this site and would appreciate some formatting help.
I created a new Word doc and copied and pasted some graphics and some
simple text boxes in to it. The graphics and text boxes were created
in Powerpoint by someone else. Looks fine on my screen.
Before sending it out via e-mail to an Outlook e-mail group I have, I
first sent myself a test message to see what it would look like on the
receiving end. It looks a mess! The text boxes are all over the
place, as is the text that is not in a box. Also, everything slide
over to ...2003 format in 2007?
I'm used to Outlook 2003, but I just bought a new computer and installed
Outlook 2007. I view a week at a time, and I prefer the calendar format in
2003 -- it shows the whole week in two columns and I didn't have to scroll.
Is there a way I can use the same format in 2007?
No, unfortunately that view had to be removed to enabled calendar overlay.
Try a two week view: http://slipstick.me/2wk
Diane Poremsky [MVP - Outlook]
Outlook Tips: http://www.outlook-tips.net/
Outlook & Exchange Solutions Center: http://www.slipstick.com/
Outlook Tips by email:
mailto:dai...Average & eliminating zero value Need HELP !! PLEASE
In cell C4 im calculating average hours for D5:D65 of all employees
In cells Z5:Z65 If L is entered means laid off
In cells AB5:AB65 IS AVERAGE OF ALL EMPOLYEES HOURS in cells D5:D65 if L is
entered in Z Column
In cells D5:D65 IS all employees hours
FORMULA FOR C4 IS
Formula for cells AB5:AB65 IS
Formula for D5:D65 IS
I want to average all employees hours except if L is entered in Z Column so
say 65 empl...formatting #2
Is the strikethrough formatting option no longer available in Publisher? Or
if you need to use a strikethrough do you have to draw a line through the
text? The line does not always stay in the same spot when you group and then
have to paste on another page.
> Is the strikethrough formatting option no longer available in
> Publisher? Or if you need to use a strikethrough do you have to draw
> a line through the text? The line does not always stay in the same
> spot when you group and then have to paste on another page.
You have the an...Logical Filtering based on Value
I've been using this formula to sort some values and return results
--I used ALT-ENTER between each formula so as to see thing clearly
Column A values can be "B01"
Column B values can be "BRN"
Columns I & J are values, one being lowest the other being highest
On another sheet I...Text formatted email
I am trying to email a long hyperlink path to some one that can only receive
text formatted emails.
When they receive the email it, the hyperlink gets converted in to 2 lines,
top line is a hyperlink (path incomplete) the second line is "text" with the
balance of the hyperlink path.
I placed < > around the hyperlink and it still splits in to 2 lines.
Is there any way to keep the full link path intact?
Tiny URL to the rescue! :-)
Robert Sparnaaij [MVP-Outlook]
Tips of the month:
-What do the Outlook Icons Mean?
-Cr...Find a value in cells
I have something like this :
I'm doing a max function in the number column and it returns me '5'
But, after that, I want to know the letter which corresponds to the max
How can I do that?
nicgendron's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25151
View this thread: http://www.excelforum.com/showthread.php?threadid=386417
Let's say that...Trying to get a type of video file format to open and run on my we
I created a video using Windows Movie maker and saved as .wmp file. I saved
it to my computer and then inserted on my web page under Front Page 2003.
Nothing! I then converted the file to avi format - still nothing! I then
tried mpeg4 still nothing. The manual is next to useless on this subject.
Anyone tell me what I need to do so that when a user opens up the page the
video plays automatically.
Save as a .wmv and insert a suitable player into the page.
For IE only,
Insert->Web Component->Advanced Controls->ActiveX Control
Choose Window...Import data not in correct format
Import data reads: Need to do statistical anaysis on hundreds of numbers
Cell value # of Cells don't want to enter the numbers by hand.
...How to force the user to fill in values for yes/no fields ?
I'm using MS Access 2003.
I have a table with a number of yes/no fields.
When a new record is started,
I want the user
to explicitly specify a value for each of these fields,
e.g. by choosing one of two radio buttons associated with the field.
Now, it was no problem for me to arrange an option group with two suitable
radio buttons for each of these fields.
The user can then choose explicitly between yes and no.
However, all fields seem to have implicit values immediately after a new
record has been started.
Therefore, the new record can be stored in the db without the user making...Four Conditional Formats?
I am attempting to set up a template for someone else to use on another
machine, and wish conditional format a cell such that it has four
The first is based on an adjacent cell value, and if this is met, then
it is colour 1.
The remaining three, are all AND statements involving the first cell
and another cell value being less than date A, less than date B or less
than date C.
Is this possible to do? I have limited VB knowledge, and am wary of
using add-ins as the people I am sending this to to use have limited
-...finding values in a sum
if you have an array of numbers and want to identify
which of those numbers add up to a specified value, is there a function
in excel that can help you to find the correct combination of numbers.
eg in a simple example; if the array of numbers was 2,3,5,6 and the
specified value was 9, we know the only combination of numbers from this
array that would sum to give the value 9 are 6 & 3, however
with a larger array of numbers (20) or more, it would be more difficult
solve the problem manually.
See my previo...Conditional Format
I want to assign a number to a certain conditional format. When the
condition is true it should put a 1 in the cell and when the condition is
false a zero or nothing should be assigned. Anyone? I seem to make the wrong
code all the time.
Use the same test that the CF uses, and return 1 for TRUE and "" for FALSE.
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Jootje" <Jootje@discussions.microsoft.com> wrote in message
>...my formats get pushed out when I insert columns
I am having trouble when inserting columns in a large data field.
Whenever I insert a column at some point in the data field, th
formulas within the data field adjust, or 'stretch', to accomodate th
added column, however my conditional formatting gets pushed along t
the right and doesn't adapt itself in the same way the formulas do.
This means whenever I insert a column, the formulas remain correct bu
I have to reset the conditional formatting for the entire data field.
I would be extremely grateful for any advice on this situation.
Message pos...Value based Purchase Order
We have a customer in the service industry using GP Ver 9.0. GP does not
seem to be addressing few key requirements for the service industry and hence
request your advice.
1. The Customer requires entering the purchase orders with the value and
without any quantity. This is basically for the service type purchase orders.
They would like to partially receive and invoice based on the value and not
based on the quantity.
As a standard functionality, the purchase order screen in GP in the
Purchasing module will not allow to enter the value (Extended Cost) without
the Quantity colum...Set a value on a form
I use the following code to open a form
DoCmd.OpenForm stDocName, , , stLinkCriteria
Is there any way to pass data to the form (through the constructor) or to
set the value of a label on the form
You can use the OpenArgs argument to pass a value to the new form, and then
use the new form's Load event to read the value and put it into a control on
First step -- pass the value:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , "ValueToPass"
Second step - read the value and use it; this code goes with the Load event
of the stDocName form:
...removing duplicate values from a column
It sounds so simple that I must be overlooking the obvious answer, but I
can't seem to find a way to remove duplicate values from a column.
The case is simple: I have 600+ emailaddresses in column A, sorted
alphabetically and there's a bunch of duplicate addresses in there (some of
'em occur up to 7 times!!). Instead of manually running through the whole
list, removing the duplicates, there MUST be some soft of filter/function in
excel to do this for me... Could anyone PLEASE help me with this!?!?
NOTE: remove the spamtrap from the emailaddress
Select the em...