Condition-question 3

[geo40]

Hello,

I've to sum up our turnover for July 2003 for our Agriculture customers. So only the turnover for period 7 ( july ) from our agriculture customers will be in my report.

DATABASE
Period Customer Invoice(EUR) Area

5 A 1500 Agriculture
6 AA 1500 Government
6 B 500 Government
7 A 1000 Government
7 BB 500 Agriculture
7 BB 2500 Agriculture

MY_REPORT

Period Turnover
7 3000

Can anyone help me with the right formula?

GEO40

 

[bluedragon2]

An easy way is by doing subtotals.

I would sort the data on period, then on Area.

Do a Subtotal Sum for Area and Period.

Blue

 

[tav1035]

geo40,

Try this->
Period Customer Invoice Area Month Total
5 A € 1,500.00 Agriculture 6 € 2,000.00
6 AA € 1,500.00 Government
6 B € 500.00 Government
7 A € 1,000.00 Government
7 BB € 500.00 Agriculture
7 BB € 2,500.00 Agriculture

Add this formula in F2, under Total.
=SUMIF(A:A,E2,C:C)
Use E2 to insert the period that you want to total.

 

[xlbo]

tav - multiple conditions - period 7 AND Agriculture - SUMIF will not work here. Geo40, you have at least 3 options:

1: Pivot table. Set Period and area as page fields and Invoice as value field then select the desired conditions

2: =SUM((A2:A100=7)*(D2100="Agriculture&quot*(C2:C100))
enter using CTRL+SHIFT+ENTER as an array formula

You can also use SUMPRODUCT or DSUM - examples of which can be found in xl help

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[bluedragon2]

Geoff,

What does the * do in the array formula?

Blue

 

[tav1035]

Good point xlbo,
didn't see the second condition.
tav

 

[xlbo]

bluedragon2 - it multiplies the array results. array formulae evaluate all rows at once to get an answer. Each "test" returns a true or false whcih equates to 1 or 0 so you end up with an internal array (using the example data provided) like:

0*1*1500
+
0*0*1500
+
0*0*500
+
1*0*1000
+
1*1*500
+
1*1*2500

Obviously, any of the multiplications involving a 0, equate to 0 so the sum is:
0+0+0+0+500+2500

Hope this explains it for you


Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[KenWright]

Besides the answers you have been given, you shoul also take a look at pivot tables, which are made for this kind of data. You get a very powerful ability to shape your data pretty much anyway you want it, and the ability to create different reports with literally justa couple of clicks or so is amazing.

You can see an intro to Pivot tables from Debra Dalgleish at the following link. Once you've tasted them, you will never look back.

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

Regards
Ken..............

 

[xlbo]

Ken - that was my 1st suggestion ;-)

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[DrBowes]

Star to Geoff for the bit about array formulae, previously I had used UDFs to sum across multiple conditions.

 

[bluedragon2]

Thanks Geoff,

That is why you guys are the guru's.

Blue

 

[KenWright]

I am losing it!!!!!!!!!

I would have sworn I read through them all and never saw a Pivot Table mentioned - I am totally cracking up - Apologies Geoff

Regards
Ken..................

 

[xlbo]

s'alright Ken - you provided the link - I just mentioned it

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[tav1035]

Geo40,
This was still bugging me so I'm re-doing my post by adding a second condition (suggestions of Xlbo*s (SumProduct)).->
Period Customer Invoice Area [ Month AreaType Total ]
5 A € 1,500.00 Agriculture 7 Agriculture € 3,000.00
6 AA € 1,500.00 Government
6 B € 500.00 Government
7 A € 1,000.00 Government
7 BB € 500.00 Agriculture
7 BB € 2,500.00 Agriculture

Add this formula in G2, under Total.
=SUMPRODUCT((E2=A2:A500)*(F2=D2500),C2:C500)
Use E2 & F2 to insert the Period & AreaType that you want to total.

This solution will allow you to sum whatever you type in the cell, instead of altering the formula everytime you need to report out.
At least this should work better than my first post.
tav

 

[KenWright]

Did anyone mention Pivot tables yet???

LOL
Ken.............