Any spread sheet wizards out there?

[NozSpark]

I'm trying to set up my accounts as a spread sheet n my iPad, but I'm also trying to keep the number of sheets down to

Income

Expenditure

Summary

Income entry form

Expenditure entry form

What I'm trying to do is calculate my gross profit for each month so I need to be able to look through the date column for a particular month and add together all the corresponding cells for that month...

So if anyone knows what formula to use I'm sure I can get the rest.

 

[revjames]

I was trying to add up my miles on my excel spreadsheet the other day for the last tax year. Someone pointed out the 'formula' tab at the top of the page and I just copied and pasted it with the appropriate range of cell numbers and hey presto!

 

[NozSpark]

I'm afraid it's a bit more complicated than the SUM expression rev...

I want it to be able to look through a sheet with my accounts on for the whole year and be able to pick out and add up some values for individual months.

The old way I did it on my pc was to have a sheet per month which was a much simpler equation, but I don't want to do it that way on my iPad. (I will if I have to though)

 

[Sidewinder]

Noz,

I could do this in Excel for Windoze if that's any help, would need a few days, though, back to work with a bang tomorrow for 3 days I hope!

Need to do some work this month, only had 4 jobs so far and one of those was warranty!

Not my fault!

Dodgy motor &/or dodgy customer machine!

 

[NozSpark]

I was thinking I'd have to use HLOOKUP or something like that along with an embedded month function and then some way to add up the appropriate figures

 

[Sidewinder]

Depends on how you lay it out I would think?

 

[NozSpark]

Got the whole thing done nearly,, just got this bit to do now

 

[calltronics]

In Excel, the SumIf function adds all numbers in a range of cells, based on a given criteria.

 

[NozSpark]

Thanks to caltronics I have now got this working,, but I'd like it to be a little better

The code I'm using is

=sum if((income::table 1: :B ),4,(private))

The first argument is the lookup column, the second is the condition and the last is the sum value

What im having to do at the moment is have another formula (column B) in the income sheet =MONTH(A1) , this returns a numeric value for the month in cell A1; however if I add another row then this formula isn't automatically copied

Is there any way i can add the "MONTH" formula to the "SUMIF" one???

---------- Post Auto-Merged at 23:58 ---------- Previous post was made at 23:57 ----------

Hope that lot makes sense!

 

[calltronics]

Not sure what your describing here, but I'll have a guess.......

The formulae should be copied OK but your reference should be MONTH(A$1)

Using the "$" fixes the reference.

That way the rows stop at the same reference month row.

But the columns move along as your formulae is copied along the rows.

If i've miss understood you send me your spreadsheet and I'll have a look at it.

TestCal CalCard — Calibration Resistance Checkbox

 

[NozSpark]

Caltronics,,

Thanks for your help, I think we're at cross purposes a bit.

With

=sum if((income::table 1: :B ),4,(private))

I've got is a date in column a, MONTH(a1) in column b - which gives me a number based on the month and then the numbers to add are in the "private" column

The sumif then looks in column b to check for a particular month (number) and then does all the adding together stuff

What I'd like to be able to do is have look straight in column a, but I can't seem to embed MONTH(a?) within the SUMIF expression

Just wondering if I could use a wildcard in there????

 

[calltronics]

Sorry NozSpark now I know what you are trying to do.

Not sure though what your thinking is right!

If you want to add up each months income then you would have to do this in an individual sumif statement (where the answer goes).

To automatically add up each month for the whole period where would you put all the individual answers?

Now if you really want to go advanced........

This little ditty will take all your data extrapolate it and format it onto a new sheet.

Which would achieve what your looking for but it requires some understanding.

Once you twig the operation of it, it is a very powerful tool for doing exactly what your trying to achieve.

Advanced filter

http://www.contextures.com/xladvfilter01.htmll

Good luck!

---------- Post Auto-Merged at 22:34 ---------- Previous post was made at 22:22 ----------

or if your question was even easier than that....

If this is your table: -

01/01/2011 1 1

02/02/2011 2 2

03/03/2011 3 4

04/04/2011 4 8

function result 4

"'=SUMIF(B1:B4,MONTH(A3), C1;C4)"

 

[calltronics]

Caneoboy,

Sorry about the second l much trouble on the posting!!!!

Very very powerful the advance filter once you get your head around it.

Use it all the time now to produce reports and extract data from large spreadsheets.

As a canoeboy have your heard of DtoW?

 

[NozSpark]

Sorry NozSpark now I know what you are trying to do.Not sure though what your thinking is right!

If you want to add up each months income then you would have to do this in an individual sumif statement (where the answer goes).

To automatically add up each month for the whole period where would you put all the individual answers?

Now if you really want to go advanced........

This little ditty will take all your data extrapolate it and format it onto a new sheet.

Which would achieve what your looking for but it requires some understanding.

Once you twig the operation of it, it is a very powerful tool for doing exactly what your trying to achieve.

Advanced filter

http://www.contextures.com/xladvfilter01.htmll

Good luck!

---------- Post Auto-Merged at 22:34 ---------- Previous post was made at 22:22 ----------

or if your question was even easier than that....

If this is your table: -

01/01/2011 1 1

02/02/2011 2 2

03/03/2011 3 4

04/04/2011 4 8

function result 4

"'=SUMIF(B1:B4,MONTH(A3), C1;C4)"

If I put a cell reference in the MONTH argument won't that fix it to that particular cell?

I want it to check each cell in the 1st range for the month

 

[calltronics]

Please dowload example: -

http://testcal.co.uk/Pages/Documentation/forumexcel.xlsx

Use an array sum such as this one: -

=SUM((MONTH($B$3:$B$12)=G4)*$D$3:$D$12)

Where: -

$B$3:$B$12 = range of dates

G4 = the cell with the month value in it (Criteria)

$D$3:$D$12 = the values to be summed

The last and most important thing is to enter the formulae by pressing CTRL+SHIFT+ENTER

This tells the system it is an array function.

 

[NozSpark]

I think I may send you a copy of it to see if you van do anything with it... Remember that this spreadsheet is on my iPad..

 

[NozSpark]

Yep,

Running numbers on the iPad

 

[NozSpark]

TBH I don't know,,, I created it on my ipad

 

[NozSpark]

I'll email it to you if you like.

It seems that numbers has some of it's own tricks,,, can have several tables on each sheet

PM me your address