FORMULA PROBLEM 1

[ptweb]

Hello, I have this forumla


=SUMIF('EXPENSES SALES'!E61:E574,B15,'EXPENSES SALES'!H61:H574)

Instead of gathering totals in this region H61:H574 I would like also to get the total from the row id entered into cell B15 ... to try and make in clear, get some totals of all rows and have the value entered into cell B15 from ranges E61:E574 and get totals in H61:H574 plus the row number also, hope this is clear enough.

 

[KenWright]

Might be just me, but I'd say about as clear as mud

I've got the SUMIF formula, that pulls what it should I assume, and you don't want to change that. I'm assuming though that you want to pull something else IN ADDITION to that, and ADD it to the result of the SUMIF formula - Is that correct?

Are you saying you want to add the ROW number to the total that the SUMIF formula is pulling?

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[ptweb]

Yes, add everything associated to number in cell B15 and then get that row info also so if it .... here is some more info... as you can see below if I put 220 in CELL B15 i locate the 220's below then get totals H's, I then want to add the row value from row 220




leyland daf recovery yes 998.75
battery 29.36
tools 198 3.00
merc 190 j****n 223 no 320.00
merc 190 commission 223 52.00
rover 200 coupe r***kou 225 no 550.00
rover 200 coupe commission 225 67.00
paint 198 24.99
parts ldv recovery 220 32.88
mot ldv recovery 220 no 65.00
parts ldv recovery 220 3.08
tax ldv recovery 220 no 96.25

 

[arthurbr]

Do you want to add the row value of your lookup value ( in this case 15) or the row values of every value found?

 

[ptweb]

I have every value, I want to lookup also the row value of the same cells, in this case H

I currently lookup E cells and then get values from H cells, I then want to get the row value of the same value in the above example 220

 

[KenWright]

If this were your data, are you saying you would want the value 220 in B15 to return the following:-

    B           C               D     E          H
10 paint                        198            24.99 
11 parts    ldv recovery        220            32.88 
12 mot      ldv recovery        220    no      65.00 
13 parts    ldv recovery        220             3.08 
14 tax      ldv recovery        220    no      96.25 


32.88 + 11 +
65.00 + 12 +
 3.08 + 13 +
96.25 + 14

= 247.21 ????

OR, are you saying that you just want to add whatever value is in ROW 220 in COLUMN H, ie H220????

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

If the latter then just add

+INDIRECT("H"&B15)

to your SUMIF formula

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[ptweb]

Sorry for delay, no thats not it.... 220 is the row id ... everything related to that purchase will be given an row id so has to allow what and where the expense was for... in other words

220 parts ldv recovery 15000.00
221 parts ldv recovery 220 32.88
222 mot ldv recovery 220 no 65.00
223 parts ldv recovery 220 3.08
224 tax ldv recovery 220 no 96.25

So I need

=SUMIF('EXPENSES SALES'!E61:E574,B15,'EXPENSES SALES'!H61:H574) + ????? rownumber relating to these expenses

Many thxs

 

[KenWright]

What is the TOTAL VALUE you would expect to get based on the example you have given??

Please also spell out in plain english every number in your example that you believe meets your criteria and would contribute to that Total value, because it sounds very much like the first scenario I surmised.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[ptweb]

Its as before

first row is row numbers.............

220 ldv recovery 15000.00
221 parts ldv recovery 220 32.88
222 mot ldv recovery 220 no 65.00
223 parts ldv recovery 220 3.08
224 tax ldv recovery 220 no 96.25

fourth row is the row number these extra costs relate to...

ie: this one
220 ldv recovery 15000.00

So total should be this:

220 ldv recovery 15000.00

plus this

221 parts ldv recovery 220 32.88
222 mot ldv recovery 220 no 65.00
223 parts ldv recovery 220 3.08
224 tax ldv recovery 220 no 96.25


because each of them relate to that row 220 this
220 ldv recovery 15000.00

total should be 15197.21

 

[KenWright]

COLUMN!!!!!!! NOT ROW!!!!!!!

In which case all I think you need is two SUMIFs

So assuming you actually have Row number sin your first column, and that column is column A, then:-

=SUMIF('EXPENSES SALES'!E61:E574,B15,'EXPENSES SALES'!H61:H574)+SUMIF('EXPENSES SALES'!A61:A574,B15,'EXPENSES SALES'!H61:H574)

or why would this not work?

Regards
Ken................




----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[ptweb]

No row, not column sorry, its the row id produced by excel, the row number, the id of thr row,

 

[KenWright]

Sorry but you have me as confused as hell now. If you are tralking about the number that you see in grey on the laft hand side of the sheet that is in fact the ROW number, then one of the first solutions I gave you should work, ie the use of INDIRECT along with the ROW number:-

=SUMIF('EXPENSES SALES'!E61:E574,B15,'EXPENSES SALES'!H61:H574)+INDIRECT("H"&B15)

Does that not give you what you wanted?

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[ptweb]

I see so the INDIRECT function will surfice?

 

[ptweb]

This is neally there Ken,
=SUMIF('EXPENSES SALES'!E61:E574,B15,'EXPENSES SALES'!H61:H574)+INDIRECT("H"&B15)

Its just I'm reaching the row from the EXPENSES SALES sheet?

Many thxs advance

 

[KenWright]

Dohhhh - my bad - apologies

=SUMIF('EXPENSES SALES'!E61:E574,B15,'EXPENSES SALES'!H61:H574)+INDIRECT("'EXPENSES SALES'!H"&B15)

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[ptweb]

Urrrrrrrrrrrrrrrrrrr the mannnnnnnnnn

 

[KenWright]

LOL - You're very welcome

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------