Conditional Sum
 

I have a big excel sheet with different colums headings.
at the bottom i wanted a conditional sum basing on 3 columns
May be a example of my question be like as follows

column a column b column c
(town) (description) (volume)

vijayawada sales 23
guntur credit sales 27
vijayawada sales 30
guntur credit sales 40
nellore sales 55
vijayawada credit sales 25

computer has to sumup the corresponding values of sales and credit sales
separately for vijayawada, guntur, nellore

thanks in advance

Conditional Sum
 

=SUMPRODUCT(--(A2:A20="vijayawada","-B2:B20="sales"),C2:C20)

etc.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"vijaydsk1970" wrote in message
...
I have a big excel sheet with different colums headings.
at the bottom i wanted a conditional sum basing on 3 columns
May be a example of my question be like as follows

column a column b column c
(town) (description) (volume)

vijayawada sales 23
guntur credit sales 27
vijayawada sales 30
guntur credit sales 40
nellore sales 55
vijayawada credit sales 25

computer has to sumup the corresponding values of sales and credit sales
separately for vijayawada, guntur, nellore

thanks in advance

Conditional Sum
 

Dear Bob,
Thanks for the effort
but the formula result is #value.
may be please check up the quotes you mentioned

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20="vijayawada","-B2:B20="sales"),C2:C20)

etc.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"vijaydsk1970" wrote in message
...
I have a big excel sheet with different colums headings.
at the bottom i wanted a conditional sum basing on 3 columns
May be a example of my question be like as follows

column a column b column c
(town) (description) (volume)

vijayawada sales 23
guntur credit sales 27
vijayawada sales 30
guntur credit sales 40
nellore sales 55
vijayawada credit sales 25

computer has to sumup the corresponding values of sales and credit sales
separately for vijayawada, guntur, nellore

thanks in advance

Conditional Sum
 

You guys have to get away from the "universal" use of SUMPRODUCT... ;-)

Your need is much more simply met by creating a pivot table...

"vijaydsk1970" wrote in message
...
I have a big excel sheet with different colums headings.
at the bottom i wanted a conditional sum basing on 3 columns
May be a example of my question be like as follows

column a column b column c
(town) (description) (volume)

vijayawada sales 23
guntur credit sales 27
vijayawada sales 30
guntur credit sales 40
nellore sales 55
vijayawada credit sales 25

computer has to sumup the corresponding values of sales and credit sales
separately for vijayawada, guntur, nellore

thanks in advance

Conditional Sum
 

Try Bob's suggestion with a typo fixed:

=SUMPRODUCT(--(A2:A20="vijayawada"),--(B2:B20="sales"),C2:C20)

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

vijaydsk1970 wrote:

Dear Bob,
Thanks for the effort
but the formula result is #value.
may be please check up the quotes you mentioned

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20="vijayawada","-B2:B20="sales"),C2:C20)

etc.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"vijaydsk1970" wrote in message
...
I have a big excel sheet with different colums headings.
at the bottom i wanted a conditional sum basing on 3 columns
May be a example of my question be like as follows

column a column b column c
(town) (description) (volume)

vijayawada sales 23
guntur credit sales 27
vijayawada sales 30
guntur credit sales 40
nellore sales 55
vijayawada credit sales 25

computer has to sumup the corresponding values of sales and credit sales
separately for vijayawada, guntur, nellore

thanks in advance








--

Dave Peterson

Conditional Sum
 

Let me follow up...

The problem in using SUMPRODUCT is that if you want a complete report for
all Towns, then you have to know what all the unique Towns are in advance.
If you have a large file, how do you propose to do that?

Let's say you have 25 different towns in your file, then you need 25
separate formulas if you want a complete report.

The much simpler method is to use a Pivot Table. You don't need to know
anything about your detail data, you only need to create the pivot table
with "Town" as the row variable, and "Description" as the column variable,
and "Volume" as the table contents. Then all combinations of Town and
Description will be created for you in a nice table (it's all drag and
drop). Ignore data you don't want.

Or, you can also use the row variable, Town, as a filter item for the pivot
table, if you only want to display one town at a time...

Much more powerful tool than trying to create a bunch of SUMPRODUCT
formulas...


"vijaydsk1970" wrote in message
...
I have a big excel sheet with different colums headings.
at the bottom i wanted a conditional sum basing on 3 columns
May be a example of my question be like as follows

column a column b column c
(town) (description) (volume)

vijayawada sales 23
guntur credit sales 27
vijayawada sales 30
guntur credit sales 40
nellore sales 55
vijayawada credit sales 25

computer has to sumup the corresponding values of sales and credit sales
separately for vijayawada, guntur, nellore

thanks in advance