Database question

mcleod15

New Member
Joined
Feb 3, 2005
Messages
14
I have a database with three columns: Looks similar to this:

Customer Meter Size Consumption (000's gallons)
COM-CITY 5/8" 0
COM-CITY 5/8 0
COM-CITY 5/8 1
COM-CITY 1.0 1
COM-CITY 1.5 2
COM-CITY 2.0 3
COM-CITY 5/8 1
COM-CITY 1.0 2
COM-CITY 5/8 1
COM-CITY 5/8 1
COM-CITY 1.0 2
COM-CITY 2.0 4
COM-CITY 3.0 6
COM-CITY 5/8 1


For Example: I am trying to find out what function I could use to find out what the total consumption is for all 5/8 meters with with consumption level less than or equal to 1(000gallons), less than or equal to 2, etc.

Would sumproduct work? Any help would be greatly appreciated. Thanks.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

=SUMPRODUCT(--(B2:B15=F2),--(C2:C15<=F3),C2:C15)
Book1
ABCDEF
1
2COM-CITY5/805/8
3COM-CITY5/802
4COM-CITY5/815
5COM-CITY1.001
6COM-CITY1.502
7COM-CITY2.003
8COM-CITY5/81
9COM-CITY1.002
10COM-CITY5/81
11COM-CITY5/81
12COM-CITY1.002
13COM-CITY2.004
14COM-CITY3.006
15COM-CITY5/81
Sheet5
 

mcleod15

New Member
Joined
Feb 3, 2005
Messages
14
Thanks. I appreciate it. Is there a way with sumproduct to figure out the consumption level (i.e. between 30 and 40) at a certain meter size. Similar to what you have just showed me?
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Sure

Add a condition:

=SUMPRODUCT(--(B2:B15=F2),--(C2:C15 > =F3),--(C2:C15 < G3),C2:C15)

F3 is min value G3 is max value.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,835
Messages
5,855,910
Members
431,772
Latest member
dannyboi1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top