Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default sum a column based on multiple other columns being true

I am trying to sum amounts based on meeting multiple criteria in other
colums. I saw the response to the "contruction guy" posted here, but it does
not work for my information?

Month Client Amt
Jul Smith 5
Jul Smith 2
Aug Smith 3
Aug Scott 10

I want to calculate total amount per client each month using "sum" and "if"
statements.

Thank you,

sofia
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10,593
Default sum a column based on multiple other columns being true

=SUMPRODUCT(--(A2:A20="Jul"),--(B2_B20="Smith"),C2:C20)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sofia" wrote in message
...
I am trying to sum amounts based on meeting multiple criteria in other
colums. I saw the response to the "contruction guy" posted here, but it

does
not work for my information?

Month Client Amt
Jul Smith 5
Jul Smith 2
Aug Smith 3
Aug Scott 10

I want to calculate total amount per client each month using "sum" and

"if"
statements.

Thank you,

sofia



  #3   Report Post  
Posted to microsoft.public.excel.programming
Ken Ken is offline
external usenet poster
 
Posts: 207
Default sum a column based on multiple other columns being true

Sofia

You can use a multiple criteria sum function (array function) like:

{=SUM(($A$2:$A$5="Jul")*($B$2:$B$5="Smith")*$C$2:$ C$5)}

This is entered as an array function (shift-ctl-enter). Athough, a
sumif function looks intuitively like something that should work, it
does not work with multiple criteria as you a have.

Good luck.

Ken
Norfolk, Va



sofia wrote:
I am trying to sum amounts based on meeting multiple criteria in other
colums. I saw the response to the "contruction guy" posted here, but it does
not work for my information?

Month Client Amt
Jul Smith 5
Jul Smith 2
Aug Smith 3
Aug Scott 10

I want to calculate total amount per client each month using "sum" and "if"
statements.

Thank you,

sofia


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,069
Default sum a column based on multiple other columns being true

Why not use a pivot table? Put the Month as a page field or row field, put
the Client as a row field, and put the Amt as the data field.

If you don't want to use a pivot table, you will have to set up a separate
formula for each combination of criteria:

=SUMPRODUCT(--(A2:A5="Jul"),--(B2:B5="Smith"),C2:C5)
=SUMPRODUCT(--(A2:A5="Aug"),--(B2:B5="Smith"),C2:C5) etc.

Hope this helps,

Hutch

"sofia" wrote:

I am trying to sum amounts based on meeting multiple criteria in other
colums. I saw the response to the "contruction guy" posted here, but it does
not work for my information?

Month Client Amt
Jul Smith 5
Jul Smith 2
Aug Smith 3
Aug Scott 10

I want to calculate total amount per client each month using "sum" and "if"
statements.

Thank you,

sofia

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default sum a column based on multiple other columns being true

Wow, you are great! I want to use this number in a separate worksheet so I
rather have the formula than a pivot table, but I will try both ways to see
what works better for me.

Thank you,

sofia

"Tom Hutchins" wrote:

Why not use a pivot table? Put the Month as a page field or row field, put
the Client as a row field, and put the Amt as the data field.

If you don't want to use a pivot table, you will have to set up a separate
formula for each combination of criteria:

=SUMPRODUCT(--(A2:A5="Jul"),--(B2:B5="Smith"),C2:C5)
=SUMPRODUCT(--(A2:A5="Aug"),--(B2:B5="Smith"),C2:C5) etc.

Hope this helps,

Hutch

"sofia" wrote:

I am trying to sum amounts based on meeting multiple criteria in other
colums. I saw the response to the "contruction guy" posted here, but it does
not work for my information?

Month Client Amt
Jul Smith 5
Jul Smith 2
Aug Smith 3
Aug Scott 10

I want to calculate total amount per client each month using "sum" and "if"
statements.

Thank you,

sofia

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum based on arguements from multiple columns Craig Excel Discussion (Misc queries) 2 October 24th 08 05:46 PM
Combine multiple columns into two long columns, Repeating rows in first column [email protected] Excel Discussion (Misc queries) 2 July 31st 06 09:45 PM
Combine multiple columns into two long columns, Repeating rows in first column [email protected] Excel Discussion (Misc queries) 0 July 31st 06 05:07 PM
Filter based value in multiple columns Melissa Excel Discussion (Misc queries) 1 October 5th 05 09:25 AM
counting in one column when two expressions in two other columns are true Henrik Excel Worksheet Functions 3 December 1st 04 05:28 PM


All times are GMT +1. The time now is 11:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"