SUMIF and IF

  • Thread starter Thread starter james
  • Start date Start date
J

james

Hello,

I have a spreadsheet with three columns, A: DATE,
B: LOCATION and C: TONNAGE.

I want to make a formula in column D to
SUM (C:C) IF(B:B)=x and (A:A)=y

where x & y are values that I pick. I think that it is a
combination of functions but I can't get the syntax
right. Could someone help me??

Thanks

James
 
james said:
Hello,

I have a spreadsheet with three columns, A: DATE,
B: LOCATION and C: TONNAGE.

I want to make a formula in column D to
SUM (C:C) IF(B:B)=x and (A:A)=y

where x & y are values that I pick. I think that it is a
combination of functions but I can't get the syntax
right. Could someone help me??

Thanks

James
Click to expand...

You need something along these lines:
=SUMPRODUCT((A1:A100=y)*(B1:B100=x),C1:C100)

The ranges cannot be whole column (e.g. A:A), so you need to adjust them to
suit. However, blanks cause no problem, so you can use ranges that are much
longer than your data if you wish. Just make sure you keep all ranges the
same length.

The x and y values can be entered directly into the formula., Alternatively,
you can put cell references in the formula and the x and y values in these
cells, which makes it versatile and easy to use. This method also gets over
how you enter the value for a date; in a cell, you just have a date, but
directly in the formula you would need to use DATE(2003,11,14) or whatever.

Note that you can increase the number of criteria if required, and you can
use operators other than 'equals'. For example, to get date falling within a
range p to q, we could use
=SUMPRODUCT((A1:A100>p)*(A1:A100<=q)*(B1:B100=x),C1:C100)
 
Back
Top