Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying to solve a conditional-problem in excel which is very similar to this question

However I need to do the following:

If

product code in range A1:A100 = A certain code

and

month in range B1:B100 (format 24/06/2011)= a certain month (numerical value e.g. 6)

then

sum all numbers in column C1:C100 for which the above conditions are true


So far I am using an additional table where I check the condition for each row and afterwards I sum up all occurences in this additional table. but I feel that there is a way to get rid of this additional table.

One of the key prblems seems to be the extraction of the month from a date range within a sumproduct or countifs function.

share|improve this question
up vote 2 down vote accepted

Enter as an array formula (press Ctrl-Shift-Enter rather than just Enter)

=SUM(($A$1:$A$100="Your Code")*(MONTH($B$1:$B$100)=6)*$C$1:$C$100)
share|improve this answer
    
Thank you for the code. It is working now. Arrays in excel escaped me for years as it seems.... – Martin H Oct 28 '11 at 11:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .