Adding in Excel Revised

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cb - 07 Oct 2007 20:18 GMT

I have a spreadsheet from Pay pal.
It lists many columns and cells.
For example in one of the COLUMNS (column D) it lists the words"us postal"
which are postal fees I incurred, for debits and their are names listed
beneath them as credits for paid sales. The actual numbers show in column H
that I need to add up that are currently showing as debits. I do not want to
add the credits in column H.

Since Column H shows debits and credits- I need to separate these out and
add the debits for total ship fees.

I want to add up only the "us postal" fees in total IN THE COLUMN H that
show as debits, so I can get a total for the total ship fees paid.

How do I do this to separate this out?
The column listed for the postal fees starts with D2 and the column listed
for the actual numbers that show as debits start in column H2

This formula was not working- or I did not replace the formula correct.
So how do I do this?

I was given this formula, but it was not working . I replaced the values
which I thought were correctly but it was not working.
=SUMIF(A1:A100,"us postal",B1:B100)

I am looking at adding the credits from row 2 through row 178

Reply to this Message

George Gee - 07 Oct 2007 20:50 GMT

Why didn't you show us what formula you actually used?

Try this: =SUMIF(D2:D178,"us postal",H2:H178)

George Gee

>I have a spreadsheet from Pay pal.
> It lists many columns and cells.
[quoted text clipped - 25 lines]
>
> I am looking at adding the credits from row 2 through row 178

Reply to this Message

cb - 07 Oct 2007 21:18 GMT

I placed this formula below in the browser formula bar and hit enter. Nothing
happens, I see zeros a the end of the page where the cursor is. Is their
another way to enter this formula and where?

> Why didn't you show us what formula you actually used?
>
[quoted text clipped - 31 lines]
> >
> > I am looking at adding the credits from row 2 through row 178

Reply to this Message

cb - 07 Oct 2007 21:19 GMT

I am adding this formula you wrote below in the browser bar. Nothing happens
at the bottom I see the # 0
Is there another place or way to enter this formula?

> Why didn't you show us what formula you actually used?
>
[quoted text clipped - 31 lines]
> >
> > I am looking at adding the credits from row 2 through row 178

Reply to this Message

George Gee - 07 Oct 2007 21:29 GMT

What are the actual contents of the cells that contain the words > us
postal?
For the formula to "work", the cells must only contain the words us
postal.
Try replacing "us postal" in the formula, with whatever is in the cells,
(you will
need to enclose the words within quotes "").

George Gee

>I am adding this formula you wrote below in the browser bar. Nothing
>happens
[quoted text clipped - 43 lines]
>> >
>> > I am looking at adding the credits from row 2 through row 178

Reply to this Message

George Gee - 07 Oct 2007 21:54 GMT

What are the actual contents of the cells in column H ?
The cells need to contain only a number, if the cells contain
say, $12 Debit, then the formula will return a "0".

George Gee

> What are the actual contents of the cells that contain the words > us
> postal?
[quoted text clipped - 54 lines]
>>> >
>>> > I am looking at adding the credits from row 2 through row 178

Reply to this Message

cb - 08 Oct 2007 04:28 GMT

These are some examples of the numbers in the column named GROSS in about
over 350 rows
the smaller numbers are the postal fees and the larger numbers are sales

GROSS
-8.35
-8.35
149.95
-8.35
149.95
-7.6
-8.35
149.95

These are the examples of the column named NAME in over 350 rows
US Postal Service
US Postal Service
The Daguerreian Society
US Postal Service
Brian Thimesch
US Postal Service
US Postal Service
Patricia Markind

To the left of the column Pay Pal has the header as NAME and then all below
they list it as US Postal Service and then aside from the larger numbers it
says the persons personal name. Why they are combining the ship fees and the
actual sales not sure.

Why they do it this way- I am not sure. I do believe if Pay Pal was a
Microsoft Company the data would be more user friendly in ways they would set
up their database for sellers to download these files with more functionality.

Ebay and Pay Pal are excellent companies, but the Pay Pal reports need
improvement on the functionality of the way we can get the info-another topic
for another time of course.

PS I am working with a trial download version of Office 07

> What are the actual contents of the cells in column H ?
> The cells need to contain only a number, if the cells contain
[quoted text clipped - 60 lines]
> >>> >
> >>> > I am looking at adding the credits from row 2 through row 178

Reply to this Message

cb - 08 Oct 2007 03:49 GMT

Okay I replaced it with US Postal Service- the figure it gave me at the
bottom to the left of the column has to be incorrect, Because I estimate what
it should be by avergaging 393 sales with a ship fee of about $15 per sale, I
am looking at over 4K. It gives me a figure of just under $700

> What are the actual contents of the cells that contain the words > us
> postal?
[quoted text clipped - 53 lines]
> >> >
> >> > I am looking at adding the credits from row 2 through row 178

Reply to this Message

George Gee - 08 Oct 2007 06:25 GMT

In your original post, you asked:
< I am looking at adding the credits from row 2 through row 178>
Now it seems that you have at least 393 sales!

You need to extend the range of the formula to include
all the rows that contain your credits.

Try: =SUMIF(D:D,"US Postal Service",H:H)

George Gee

> Okay I replaced it with US Postal Service- the figure it gave me at the
> bottom to the left of the column has to be incorrect, Because I estimate
[quoted text clipped - 62 lines]
>> >> >
>> >> > I am looking at adding the credits from row 2 through row 178

Reply to this Message

Niek Otten - 07 Oct 2007 20:55 GMT

Better to stay in the thread you started.

There may be spaces or other invisible characters in your data.
This often happens when you import data from other applications.
Use the LEN() function to see how many characters there really are in the cell and compare that with what you see.
Use the TRIM function to remove all spaces except single spaces between words.
Use the CLEAN function to remove all nonprintable characters. HTML characters can be removed with a macro by David
McRitchie,
which can be downloaded here: http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a spreadsheet from Pay pal.
| It lists many columns and cells.
[quoted text clipped - 22 lines]
|
| I am looking at adding the credits from row 2 through row 178

Reply to this Message

Rate this thread: