summing data based on two columns

Question

i have a spreadsheet with multiple columns of data.  I want to get subtotals based on two columns.  Is this possible?  so if column G and column H contain the exact same information - i want to subtotal column I

Answer 1

i think i did not pose my question properly and hope once i make it clearer maybe you will have an idea.

here is how columns G and H appear:

Defined	1,2,3,4,5
Other	1,2,3,4,5
Other	1,2,3,4

| Other | 1,2,3,4 | | Defined | 1,2,3,4,5 | | Other | 1,2,3,4,5 | | Other | 1,2,3,4 | | Defined | 1,2,3,4,5 |

(column A includes company information so the data is sorted by company first)

let's say the first 4 rows are from the same company.  i have $ information in column E.  I need totals for every time columns G and H contain the same information.  in the example above - i would need a total for row 1, a total for row 2 and a combined total for rows 3 and 4 (other - 1,2,3,4 in both g and h for that row)

sorry for my confusion!  not sure if this is even possible.

Answer 2

I was incorrect when i said we had excel 2007 - it is 2003.  i dont think sumifs is available until 2007.   thanks for your quick response though.  cant wait for a higher version

Answer 3

... so if column G and column H contain the exact same information ...

Yes, this can be done in either of the two following formulas. If the values to sum are in I2:I100 and,

If the value in G matches the value in column H (in the same row) then,

=SUMPRODUCT((G$2:G$100=H$2:H$100)*(I$2:I$100))

... or if both column G and column H match another value, say in B2, then,

=SUMPRODUCT((G$2:G$100=$B$2)*(H$2:H$100=$B$2)*(I$2:I$100))

Does one of those supply the correct value for you?

---

• "Have you tried turning it off and then on again?" - Roy from 'The IT Crowd' (Emmy award-winning British comedy)

Answer 4

Hi,

Assume that you enter the information you want to match with column G in E1 and the information you want to match in column H in E2 then you can use

=sumproduct(--(E1=$G$1:$G$1000),--(E2=$H$1:$H$1000),$I$1:$I$1000)

change ranges to fit yours

Answer 5

i have a spreadsheet with multiple columns of data.  I want to get subtotals based on two columns.  Is this possible?  so if column G and column H contain the exact same information - i want to subtotal column I

See if this is what you had in mind.

Let's assume this is your data in the range G2:I10

B	H	75
A	B	57
B	A	49
B	D	69
B	F	54
A	B	21
A	B	53
B	A	80
G	D	25

You want to sum column I where the corresponding cells in column G = A and column H = B.

=SUMIFS(I2:I10,G2:G10,"A",H2:H10,"B")

Better to use cells to hold the criteria:

• K2 = A
• L2 = B

=SUMIFS(I2:I10,G2:G10,K2,H2:H10,L2)

--

Biff

Microsoft Excel MVP