SUMIF with an AND

[1troy123]

hey,

I want to write a formula that can search for a date and an area in list of information and the calculate the Qty given


A B C
02/06/14 Area1 30
02/06/14 Area2 25
02/06/14 Area1 32
03/06/14 Area2 30
03/06/14 Area2 30
03/06/14 Area1 26
04/06/14 Area1 30
04/06/14 Area2 30
etc.

there are other fields such as different product codes but they aren't important for this search which is the reason the data looks repeated

can anyone help?

[zbor]

Instead of SUMIF use SUMIFS formula (for XL 2007+).
then you can SUM by several criteria.

[Fotis1991]

If you use Excel>=2007 take a look to SUMIFS function or else to SUMPRODUCT.

[newdoverman]

In case you are not familiar with what the first two answers are referring to, this is to show how they can be implemented.

	A	B	C	D	E	F	G	H	I	J
1	02/06/2014	Area1	30		Date	Area
2	02/06/2014	Area2	25		03/06/2014	Area2
3	02/06/2014	Area1	32
4	03/06/2014	Area2	30		Sumifs	60
5	03/06/2014	Area2	30		=SUMIFS($C$1:$C$8,$A$1:$A$8,E2,$B$1:$B$8,F2)
6	03/06/2014	Area1	26
7	04/06/2014	Area1	30		Sumproduct	60
8	04/06/2014	Area2	30		=SUMPRODUCT(--($A$1:$A$8=E2),--($B$1:$B$8=F2),--($C$1:$C$8))
9

[Tony Valko]

=SUMPRODUCT(--($A$1:$A$8=E2),--($B$1:$B$8=F2),--($C$1:$C$8))

No need for the double unary on the sum range:

=SUMPRODUCT(--($A$1:$A$8=E2),--($B$1:$B$8=F2),$C$1:$C$8)

[newdoverman]

As far as that goes, there isn't a need for the brackets either as you have shown....force of habit.

[1troy123]

thanks the sumifs works a treat

[newdoverman]

Thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.