Sum cells in columns based on condition

  • Thread starter Thread starter Manosh
  • Start date Start date
M

Manosh

Dear all,
I would like to multiply and add two columns together using an
appropriate function - best if i could avoid anything too complicated
like vba :-P
The criteria for this is to multiply and add rows in the columns
following the A's

Col1 ... Col5
A
1 2000
H
2 1000
A
2 2000

So the sum should read 1x2000+2x2000 = 6000
(Col2 to Col4 also contain values as Col1.)
I'm not sure how to do this...
- I can't put the A's in any other place other than above the numbers,
so the sum product doesn't work
- I've put in a conditional format to colour the cells with the
numbers green/ blue if the cell above is A/ H to see if i could use a
sum based on cell colour but can't find a function to do this.
Help!
thanks in advance
manosh
 
Hi Manosh,

Am Sun, 15 Aug 2010 10:52:15 -0700 (PDT) schrieb Manosh:
The criteria for this is to multiply and add rows in the columns
following the A's

Col1 ... Col5
A
1 2000
H
2 1000
A
2 2000

So the sum should read 1x2000+2x2000 = 6000
Click to expand...

in F2:
=A1="A" and fill down
Then:
=SUMPRODUCT(--(F2:F100=TRUE),A2:A100,E2:E100)


Regards
Claus Busch
 
Hello,

If your data are between row 1 to row 6, try this:
=SUM(IF(A1:A6="A",OFFSET(A1:A6,1,0)*OFFSET(A1:A6,1,4),0))
This is a an array formula that should be entered with the keys
Ctrl+Shift+Enter instead with the single key Enter.



"Manosh" <[email protected]> a écrit dans le message de groupe de
discussion :
(e-mail address removed)...
 
Hello,

If your data are between row 1 to row 6, try this:
=SUM(IF(A1:A6="A",OFFSET(A1:A6,1,0)*OFFSET(A1:A6,1,4),0))
This is a an array formula that should be entered with the keys
Ctrl+Shift+Enter instead with the single key Enter.

"Manosh" <[email protected]> a écrit dans le message de groupe de
discussion :
(e-mail address removed)...
Click to expand...

this seems to work, but when i drag the formula to the right (as i've
have many columns), i need to change the value of the '4' in the
second offset. i tried using a 5-column() so that i could change this
value automatically, but it doesn't work. Any ideas?!
thanks!
 
Hi Manosh,

Am Sun, 15 Aug 2010 10:52:15 -0700 (PDT) schrieb Manosh:




in F2:
=A1="A" and fill down
Then:
=SUMPRODUCT(--(F2:F100=TRUE),A2:A100,E2:E100)

Regards
Claus Busch
Click to expand...

thanks! but i have more columns between a and e that have values, and
in some cases the same row could have an "'Ä" or a "'H'', so then this
stops working...:-(
 
hello,

I am not sure how your data are arranged in your sheet.

If I assumed you have 4 sets of conditions in column A to D
If I assumed you have 4 sets of values in column E to H,
you could try:
=SUMPRODUCT(--(A$1:A$6="A"),A$2:A$7,E$2:E$7)
valid with Enter and then drag the formula to the right

If I assumed you have 4 sets of conditions in column A to D
If I assumed you have only 1 set of values in column E,
you could try:
=SUMPRODUCT(--(A$1:A$6="A"),A$2:A$7,$E$2:$E$7)
valid with Enter and then drag the formula to the right






"Manosh" <[email protected]> a écrit dans le message de groupe de
discussion :
(e-mail address removed)...
 
Back
Top