SUMIF, SUM, AND..?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If have got a problem and need to nest a function to achiev following:

I have got a database in my spreadsheet and values in square meters in the last column. My function should now sum up the sqm values if column A shows "customer1" and column B shows "product1".
In anather cell I would have the sqm sum for customer1 and product 2 and so on.....

I am trying since days to mix some functions up but I cannot find one who calculates that in one cell only.

Thanks for your help in advance
 
With column A being your customers, column B being your products, and column
C being your sqm values:

=SUMPRODUCT((A1:A10="customer1")*(B1:B10="product1")*C1:C10)

--
HTH,
Laura Cook
Appleton, WI


MATT said:
If have got a problem and need to nest a function to achiev following:

I have got a database in my spreadsheet and values in square meters in the
Click to expand...
last column. My function should now sum up the sqm values if column A shows
"customer1" and column B shows "product1".
In anather cell I would have the sqm sum for customer1 and product 2 and so on.....

I am trying since days to mix some functions up but I cannot find one who
Click to expand...
calculates that in one cell only.
 
Not sure exactly what you want....But....not something like this?

=IF((AND(B1="Customer1",C1="Product1")),SUM(B2:E2),"")

Maybe more information needed.

Hope it helps.

-Bo
 
probably some more information.
Because it is an endless spreadsheet I need to enter A:A and B:B and C:C to get the whole lot of the column.
My sheet would look like this:
A B C
cust1 prod1 100
cust2 prod1 150
cust1 prod2 140
cust1 prod1 200

My formula should now enter 300.
Lauras formula works if I put in the range of cells. But not for the endless column. But its more than I had. Thanks so far
 
MATT said:
Because it is an endless spreadsheet I need to enter A:A and B:B and
C:C to get the whole lot of the column.
My sheet would look like this:
A B C
cust1 prod1 100
cust2 prod1 150
cust1 prod2 140
cust1 prod1 200

My formula should now enter 300.
Lauras formula works if I put in the range of cells. But not for the
endless column. But its more than I had. Thanks so far
Click to expand...

Laura's formula is all there is. There's no way to perform multiple criteria
counting and summing on entire columns. Not with SUMPRODUCT, not with DCOUNT
or DSUM (the topmost row would need to be field names), not with SUMIF or
COUNTIF (only one criteria supported), not with SUM(IF(.)) (IF returns
arrays, but the arrays it returns can't exceed 65,535 rows).

Well, there is divide & conquer: while SUMPRODUCT(...A:A...) won't work,
SUMPRODUCT(...A1:A32768...)+SUMPRODUCT(...A32769:A65536...) would.

Do you really have data in all 65,536 rows of these columns, or do you only
have a lot of data (fewer than 65,536 rows) with an indeterminate final,
bottommost row? If the latter, it's very likely ranges like A1:A65535 or
A2:A65536 would work.
 
Back
Top