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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,388
Members
412,590
Latest member
Velly
Top