and formula 1

[gsc123]

Just rephrasing this

=SUMIF(G2:G36,0,E2:E36) I would like to add a AND condition to this sum so that H2:H36 also equal 0...

 

[GlennUK]

The easiest way to do this is to use SUMPRODUCT, like this:

=SUMPRODUCT((G2:G36=0)*(H2:H36=0)*(E2:E36))

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[gsc123]

Is this on the right track?

=IF('EXPENSES SALES'!I4:I289>0,SUMIF('EXPENSES SALES'!D4289,A26,'EXPENSES SALES'!H3:H289),"")

I'me talking to another sheet you see

 

[GlennUK]

No, you are not on the right track ... that's if you are considering using SUMPRODUCT.

See here:

=SUMPRODUCT(('EXPENSES SALES'!I4:I289>0)*('EXPENSES SALES'!D4:D289=A26)*('EXPENSES SALES'!H3:H289))

If you are trying to use array IF formulae ( entered using Ctrl-Shift-Enter ), then maybe:

=SUM(IF('EXPENSES SALES'!I4:I289>0,IF('EXPENSES SALES'!D4:D289=A26,'EXPENSES SALES'!H3:H289,0),0))

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[gsc123]

For some reason coming back with zero here are some cells I search on
Hope this helps

D E F G H I
1 no 2250.00
4 69.00
1 no 675.00
4 63.00
1 270 no 0.00 626.01
3 0.00 9.99

 

[GlennUK]

Sorry, but I can't tell what value is in which column.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[gsc123]

D, H, I columnS are numbers SORRY

 

[xlbo]

....and the formula you are using on these numbers is.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

D, H, I columnS are numbers SORRY

The first 4 lines have one number after the initial D column entry ... is this number supposed to be in H or I for each of these lines? Not that I think it matters, as it seems that if there is a number in column I then the corresponding cell in H appears to be zero. Or it could be that there are numbers in column H and where they are not zero there is nothing in column I.

In either case your logic would return zero for this data, at a guess.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[gsc123]

The idea is to lookup d column for a number I enter in a cell, lets say its 1, then each cost of same row that is type 1, see if column I has a figure, if not give me column H amount for all rows that have no prices in column I...

Hope thats clearer

 

[GlennUK]

how about:

=SUMPRODUCT(('EXPENSES SALES'!I3:I289=0)*('EXPENSES SALES'!D3:D289=A26)*('EXPENSES SALES'!H3:H289))+SUMPRODUCT(('EXPENSES SALES'!D3:D289=A26)*('EXPENSES SALES'!I3:I289))

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[gsc123]

Thxs for the help, it seems it still totalling all the cells 54000 when it should be around 15773

 

[GlennUK]

Without seeing it, it's hard to decide what could be going wrong. Can you send the book to:

glennbumford -at- hotmial -dot- com

changing the -at- and -dot- and spelling hotmial correctly ( call me paranoid!!!! )


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

please, for the love of pete, post the formula that isn't working. how can we suggest what is wrong when we don;t know what you are using?

Let me get this straight

You want to enter a number in a cell (Z1 for the sake of argument) - you then want to sum all the values in column H that have the same number as entered in Z1 AND do not have a number in column H ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[gsc123]

Yes, a column is a type ie: of type 1, I want to add up all these sums in column H that dont have a value in column I


COLUMNS
D H I
TYPE COST SOLD
1 10 15
1 25 THIS VALUES OFF COLUMN H
1 85 115
1 14 THIS VALUES OFF COLUMN H
1 34 12

...ETC

 

[GlennUK]

=SUMPRODUCT(('EXPENSES SALES'!I3:I289="")*('EXPENSES SALES'!D3:D289=A26)*('EXPENSES SALES'!H3:H289))

this only sums H when I is blank, and D = A26. I thought you wanted something else.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[gsc123]

BINGO

=SUMPRODUCT(('EXPENSES SALES'!I8:'EXPENSES SALES'!I294="")*('EXPENSES SALES'!D8:'EXPENSES SALES'!D294=A31)*('EXPENSES SALES'!H8:'EXPENSES SALES'!H294))


Thxs for the time

 

[xlbo]

a column is a type ie: of type 1, I want to add up all these sums in column H that dont have a value in column

For future reference, if you had described your situation like that in the 1st place, you would probably have got the correct formula 1st time - that is exactly the type of logical expression that allows people to understand your requirements. Please don't take this the wrong way - just trying to help you for the future

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[gsc123]

Ok