Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Help
I have a spreadsheet which is imported to excel from another program. I am
trying to make a formula that will give me the subtotals for each day as well as a weekly total. I tried using SUMIF, but kept getting 0. Column G has my days which are listed as Monday, Tuesday, etc. Column I has the durations I want to add. I was putting my totals in the column next to I as this spreadsheet gets imported on a weekly basis with different data for each day so none of the columns stay the same. I also have to run it for an number of people within the office. If I can get the formula to work then I am planning on recording into a macro. Any suggestions would be greatly appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Help
What does your formula look like? Something like this:
=SUMIF(G:G,"Monday",I:I) Usually, when someone says: I tried using _____, but kept getting 0. And I see this: I have a spreadsheet which is imported to excel from another program. That's a red flag to me. A common problem that occurs when importing or copy/pasting data from an external source is that you get unseen whitespace characters that cause entries to not match each other and/or numeric values that are evaluated as TEXT strings due to the whitespace characters. For example: Imported as Monday<whitespace and you're trying to match Monday. Imported as 10<whitespace and you're trying to sum (or in your case, SUMIF). So, you have to troubleshoot. Type a weekday name like Monday in a cell . Compare that cell to one of the Monday cells in your data set. A1 = manually typed Monday G10 in your data set displays Monday. =A1=G10 If they match the result will be TRUE. I10 in your data set displays as 10. =ISNUMBER(I10) If I10 is a true numeric 10 that result will be TRUE. I copy/paste tons of stuff from the web every day and I run into this problem every day. Luckily, there's an easy solution. At this website: http://www.mvps.org/dmcritchie/excel/join.htm#trimall There's a macro that will quickly "clean" your data of the most common whitespace characters that cause these problems. -- Biff Microsoft Excel MVP "RLock13" wrote in message ... I have a spreadsheet which is imported to excel from another program. I am trying to make a formula that will give me the subtotals for each day as well as a weekly total. I tried using SUMIF, but kept getting 0. Column G has my days which are listed as Monday, Tuesday, etc. Column I has the durations I want to add. I was putting my totals in the column next to I as this spreadsheet gets imported on a weekly basis with different data for each day so none of the columns stay the same. I also have to run it for an number of people within the office. If I can get the formula to work then I am planning on recording into a macro. Any suggestions would be greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
SUMIF? | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |