[Solved] If An = X and Bn = Y, add Cn

Discuss the spreadsheet application
Post Reply
Robindude
Posts: 10
Joined: Sun Oct 06, 2013 11:43 pm

[Solved] If An = X and Bn = Y, add Cn

Post by Robindude »

I have a list of values and I want the sum of another value if they are the same.

Example:
Column A = 1 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4
Column B = 1 1 1 2 2 1 1 2 2 1 1 2 2 1 1 2 2
Column C = 3 2 2 5 4 3 4 5 2 1 5 6 3 1 3 5 6

I want Column D to look like this:
Column D = 7 7 7 9 9 7 7 7 7 6 6 9 9 4 4 11 11

A1 = A2 = A3 and B1 = B2 = B3, therefore D1, D2, and D3 = C1+C2+C3

Any idea how to code this?
Last edited by Hagar Delest on Sun May 28, 2017 9:02 pm, edited 1 time in total.
Reason: tagged [Solved].
Open Office 4.0 on Win 7
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: If An = X and Bn = Y, add Cn

Post by RusselB »

I'd suggest trying the SUMIFS function.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Robindude
Posts: 10
Joined: Sun Oct 06, 2013 11:43 pm

Re: If An = X and Bn = Y, add Cn

Post by Robindude »

I've looked into the SUMIF function, but I can't figure out how to get it to check if both A and B are the same.
=SUMIF(A2:A9999;A2;C2:C9999)
The above gets the sum for every C where each A is the same A2. But I don't get how to do so only if each B is also the same as B2.
Open Office 4.0 on Win 7
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: If An = X and Bn = Y, add Cn

Post by RusselB »

The SUMIFS function is slightly different from the SUMIF function. Note the additional S in the function name.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Robindude
Posts: 10
Joined: Sun Oct 06, 2013 11:43 pm

Re: If An = X and Bn = Y, add Cn

Post by Robindude »

SUMIFS is it. Works beautifully! Thanks! The reason I didn't know about it is it wasn't in my help files. Maybe I need an update.
Open Office 4.0 on Win 7
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: If An = X and Bn = Y, add Cn

Post by RusselB »

Update not required.
At this point it's a semi-documented function.
The balloon help has it, but the help file doesn't.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Post Reply