SUM with WHERE
I have one sheet with a bunch of records that look like:
Name, Lang, Qty
Peter, English, 5
Peter, French, 2
Dave, English, 7
On a second sheet I want to create a summary of the records:
So What I am kind of looking for is this:
English, =sum of $DRECORDS where Lang=English
French, =sum of $DRECORDS where Lang=French
Any ideas how to do this in excel?
Look in the help index for SUMIF
Microsoft MVP Excel
"Peter Carlson" <peter@h_o_w_u_d_o_d_a_t.com> wrote in message
news:e1l3%233eXI...How to apply headings to all worksheets
I have a customized heading and footnote which I want to use for all
worksheets in one file. Can someone tell me how please?
contor+tab of the worksheets . all worksheets will be selected
now you type in sheet 1 whatever header you want. it will be repeated in all
remember to select any one of the sheets so that selection of all sheets is
"bill" <firstname.lastname@example.org> wrote in message
> I have a customized heading and footnote which I want to use for all
> worksheets in one fil...Sum duplicate lines in a table
I have a table which contains a list of products soted by poduct name.
Against each product there is a stock quantity, each Item may appear more
I want to be create a query which will give me a list of the products, but
with only one entry per item, with a total quantity for each item.
Is this possible?
>I have a table which contains a list of products soted by poduct name.
>Against each product there is a stock quantity, each Item may appear more
> I want to be create a query which will give me a list of the products,...Can the format of the destination cell be kept?
I keep track of the "state" of customers depending on different criteria and
for that I have conditional format to change the color of the cells.
Once a cycle ends, I need to keep record of the last state in another colum
so that the new cycle can begin. The problem is that when I copy the old
content into the new cell, it brings along its color and I need it to be
plain white. I know that I could choose paste special and then just choose
value and that would bring just the value without the color, but I want to
copy the old contents in a shorter way. So is there a way...Sum on horizontal lookup.
in one row (a10:aa10), i have a name in one cell, then value in the
next, name in one cell, then value in the next...
Some of the names in the row are repeated. Based on a lookup value
(which will be one of the names), I want to:
search the row for the lookup value
add the value to the right each time the lookup value is found.
a10 a11 a12 a13 a14 a15 a16 a17
errors 10 correct 10 errors 10 correct 20
lookup value "errors", result 20
lookup value "correct", result 30
_____________...Can I split a cell diagonally, with text in each triangle ?
I am putting together a calendar on excel and would like to split a cell
diagnolly, colour each section and be able to type text in each triangle.
Is this possible and if so how ?
No it is not possible! (Now that I have said that someone will probably come
in with a method)
Youi could, however, make two triangles with different fills, and put text
in them or make the fills partially tranparent and put text in the cells
"Helen T" wrote:
> I am putting together a calendar on excel and would like to split a cell
> diagnolly, colour each ...Running Sum 01-27-10
I have been looking at some running sum examples (http://
support.microsoft.com/kb/290136), but can not work out how to apply it
to my query.
I have Recieved, Recieved Amount .. then Cleared, Cleared Amount .. i
am trying to work out a running backlog of work.
A: Recieved, Rec Amount .. Cleared, Clrd Amount .. Backlog, Backlog
B: Recieved, Rec Amount .. Cleared, Clrd Amount .. Backlog of A -
Cleared + Received.
C: Recieved, Rec Amount .. Cleared, Clrd Amount .. Backlog of B -
Cleared + Received.
and so on?
Im guessing its a DSUM thing, but i cant make th...Formatting Cells Question
I am having troubles with formatting cells in EXCEL 2003.
I have a column of many hundreds of rows lthat contain data like
on fist glance this looks like a date but it really is not.
EXCEL on the other automatically assumes it is a date and formats as
I have tried formatting as text, General and even experimented with
custom formats to no avail.
In the same column I have
Which is dispolayed exactly as is.
How can I get EXCEL to simply display data like 01Jan12 exactly as is?
You must format the cell as Text BEFORE typing any information...Freeze cells
How do you freeze cells instead of the rows or columns.
When i am working a fuction and try to copy from aw date
formula it copys all of the cells I do not want it to
copy the top cells. You use to vbe able to freeze the
cells in Lotus can you freeze on cell in Excel????
Use absolute reference for the cells that you want to be fixed
when copied down it will increment B2 to B3 and so on but A2 will stay the
"darlene lassiter" <email@example.com> wrote in message
news:firstname.lastname@example.org...How to Reference an Array to Count Values?
I need to reference a list of names in a column in one worksheet to count the
number of times those names occur in a column in another worksheet in the
That is, check the name list and sum the number of times those names occur
in another list.
How do I do this?
Take a look at the sumif function in help.
> I need to reference a list of names in a column in one worksheet to count the
> number of times those names occur in a column in another worksheet in the
> same workbook.
>...Help on SUM cells with formulas
At F199 I have the formula =SUM(F177:F198) but rows 177 to 198 contain
formulas like =(+E180-E179) and F199 formula won't work.
Do I need to extract correctly the values only? is it possible?
What exactly does "formula won't work" mean?
What kind of return are you getting?
No answer? - Wrong answer? - Error message?
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------...Need to seperate alpha numeric data in a cell
I have a number of cells that contain words and numbers. How can I ge
rid of or seperate the numbers form the data.
e.g Cell A1 contains - Bridge Terminal 123456
I need to just get 123456
Please note numbers can be anywhere in the data and are differen
Thanks in advance
Message posted from http://www.ExcelForum.com
I think you need a user defined function (UDF) for this. Go to the VB
editor, insert a new module and enter the following:
Dim i As Integer
Dim strReturn As String
For i = 1 To Len(xstrIn)
If IsNumeric(Mid(xstrIn, i, 1)) Then st...How to Sum a column if reference column is blank
How do I sum a cloumn of numbers when my reference column is blank?
>when my reference column is blank?
What does that mean?
When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently answering the question.
Occam''''s razor (Abbrev)
> How do I sum a cloumn of numbers when my reference column is blank?
See if this is what you had in mind...
Sum A1:A5 where the corresponding cell in B1:B5 is blank/empty:
...Excel Crashing using Remote Worksheet Calls in NETWORKDAYS
We're having problems attempting with Excel 2007 crashing when referencing a
lookup table in Worksheet 'B' from Worksheet 'A' during a NETWORKDAYS
call.... In more detail...
Worksheet 'A' has a list of rows including two date columns. We're
attempting to workout the difference in working days between the two date
columns taking into account a list of "holiday" dates contained within
Worksheet 'B'. We are using NETWORKDAYS function for this.
Everything works fine whilst Worksheet 'B' is open, however when you close
Works...drop-down menus and nested references
I am trying to make a series of drop-down menus wherein the list used for the
second drop-down changes based on what is selected in the first drop-down.
Because there are more than seven options in the first menu, I can't just do
an =IF() function. Is there any way to force the calculation of a reference
within another formula? For example, if I have named lists A, B, and C and
the first drop-down menu in A1 contains the options A, B, and C, how do I get
the drop-down menu in A2 to read A1 as a list name instead of a text item?
If there is no way to do this, does anyone have alte...Sum, Sum if or if???????
I inherited a spreadsheet and everytime I save or update a cell I get
message that reads "The macros in this project have been disabled
Please refer to online help etc to enable".
How can I get rid of this message, as as far as I know I not set up an
macros and there are none in the list after checking the macro list
(the spreadsheet I have enclosed for question 2 example does this i
you enter or change an amount)!
2. Can you help with a formula, basically I want excel to provide dat
if any particular figure is over a certain amount. see attache
Thanks in ad...cell watch function in excel 2001 for mac? #2
Thanks JE McGimpsey
mike10's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1441
View this thread: http://www.excelforum.com/showthread.php?threadid=26193
...count or sum
how can I count all the occupied cells in the same column
if the cells contain text and numbers?
row 1 br1
row 4 cb2
row 7 df6
ect down to row 200.
the answer should be 3 (the number of cells occupied)
please help thanks
"jerie" <email@example.com> wrote in message
> how can I count all the occupied cells in the same column
> if the cells contain text and numbers?
> ...Linked cells and text boxes
My query is this:
I have created a questionnaire answer template comprising of two worksheets.
One contains the answers and the second is a score sheet that links to the
cells in the answer sheet.
When linking cells between the workbooks, the numerical ones are fine.
However, a lot of the answers are in text form so to get round that, I set up
text boxes in the score sheet to link to the cells in the answer sheet. That
almost works apart from one thing:
As I type the text in the cell in the answer sheet, the text box in the
score sheet does not carry all of the text. Some stop in mid...Synchronize data across 2 sheets
Excel 2007 Workbook with a sheet called 2010 around 700 rows/records and a
sheet called Previous around 5000 rows records.
Both have 31 columns and identical column headers
On the 2010 sheet there are records that have changed, how do I get the
changed records details onto and overwite the same record on the Previous
Not sure if it would help but each record has a unique reference number.
How do i do this ?
Hope this makes sense
thanks for any help offered
If you know which colums are to be changed I would use vlookup function.
Vlookup(lookup valu...Can I get a cell to automatically rezero after entering a value. H
I'm trying to create a spreadsheet that calculates inventory with a column
for initial inventory), total inventory used, Inventory remaining, and a
column where you add in the quantity used for the last order and it
automatically deducts the quantity used from the balance. I'm running into a
problem where the number that I enter in the 'quantity used' column remains
and when I make other changes in the same row, it deducts the quantity used
an additional time. If I could get the value that I enter to go to zero, or
the cell to clear after I enter a value, my prob...Recalc not working
In one of my forms I want a subform to be updated the moment I enter it and
one of it's combobox be reflecting all the entries in one of the filed in the
Private Sub CrTrans_Enter()
doesn't seem to be carryingout the same.
Thanx in advance,
On Thu, 11 Feb 2010 02:45:01 -0800, Faraz A. Qureshi
How about Me.Requery?
Microsoft Access MVP
>In one of my forms I want a subform to be updated the moment I enter it...Number above next Cell
Have a Sheet with codes :
A B C D C
1 T G G G 12
2 G G G T 11
3 G G T T 23
(each Cell is 16x16 pixels)
The G's and T's are codes (Format as "T 12" (Letter-Space-Space-Number))
The Number part should be hidden under the next Cell but in each line the
Cell it is above this Cell.
Want it to be like this :
A B C D C
1 T G G G
2 G G G T
3 G G T T
Put a space character in the cell to the right.
In article <firstname.lastname@example.org>,
"Roby" <email@example.com> wrote:
> Have a Sheet with code...Trying to create conditional format cell
trying to automate a form. Have 1st part of what I want to do but don't know
how to do the rest. Example
Cell "C" = Cell A minus cell B . Now what I want to do is this: if Cell C is
10 percent less than Cell D then text is Red, Bold, Fill is yellow. is this
Possible? Not very good at math functions.
select cell C2. Format - Conditional Format. Change first box to "Formula
is". In second box, input this:
Click the format button, on text tab, select bold, and on pattern tab,
(assuming you meant 10% less or more)
-- ...Dates in cell
I want to be able to add a date to cells without having to
use the /; however when I enter the date as 111803 the
results of the cell is 2/7/2206. What am I not doing???
Thanks in advance
Your not entering the / as you already know. Excel will not recognise it a
s a date without the /
For possible solutions and a better understanding try
Microsoft MVP - Excel
> I want to be able to add a...