SUMIF Help

[RLock13]

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!

 

[T. Valko]

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.