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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
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?
 
Upvote 0
Sure

Add a condition:

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

F3 is min value G3 is max value.
 
Upvote 0

Forum statistics

Threads
1,211,853
Messages
6,104,371
Members
447,902
Latest member
chriswebs23

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
Back
Top