Sum with multiple conditions

[mohitmahajan]

Pls guide how to sum if multiple conditions are to be set. For example,
if two or more criterias meet only then sum of data in a coloumn should
be done.

I tried sumif but it takes only one condition. Pls guide.

 

[Andy B]

Hi

One way is something like:
=SUMPRODUCT((A2:A1000="criteria1")*(B2:B1000="criteria2")*(C2:C1000))
This function cannot use full columns (A:A) and each of the ranges must be
the saze size.

Hope this helps.

 

[Sicarii]

I believe you could achieve this with conditional formatting. Which is
under Format\Conditional Formatting...

 

[mohitmahajan]

OK, let me explain it further since I am not aware of sumproduct.

In sheet one there are three coloumns A, B and C. A is for the date.
B says yes or no. C is the text data. Now I want to calculate data i
C if A and B meet the criteria.

If sumproduct helps in this case then pls let me know more abou
sumproduct as I have never used it.

Also, pls guide hoiw can conditional formatting be used in this?

Thanks for the prompt replies

 

[Frank Kabel]

Hi
SUMPRODUCT is what you're looking for. e.g. if you want to
calculate the number of 'yes' for a date range use
something like

=SUMPRODUCT((A1:A100>=DATE(2004,1,1))*(A1:A100<DATE
(2004,4,1))*(B1:100="Yes"))

to sum the corresponding values in column C use
=SUMPRODUCT((A1:A100>=DATE(2004,1,1))*(A1:A100<DATE
(2004,4,1))*(B1:100="Yes"),C1:C100)