Database question

[mcleod15]

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.

 

[fairwinds]

Hi,

Try:

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

Book1
	A	B	C	D	E	F
1
2	COM-CITY	5/8	0			5/8
3	COM-CITY	5/8	0			2
4	COM-CITY	5/8	1			5
5	COM-CITY	1.00	1
6	COM-CITY	1.50	2
7	COM-CITY	2.00	3
8	COM-CITY	5/8	1
9	COM-CITY	1.00	2
10	COM-CITY	5/8	1
11	COM-CITY	5/8	1
12	COM-CITY	1.00	2
13	COM-CITY	2.00	4
14	COM-CITY	3.00	6
15	COM-CITY	5/8	1
Sheet5

 

[mcleod15]

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]

Sure

Add a condition:

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

F3 is min value G3 is max value.