Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am attempting to set up a formula in excel that will look at a range of data which has dates specified in c7:c30, and categories Active-"A" and Completed-"C" in L7:L30. I am attempting to have excel look at each row individually and compare whether the dates are between quarter dates specified of 1/1/12 (B38) and 3/31/12 (C38) (the first quarter of the year), and if this condition is met then count those that are marked with "A" for active.

I have come up with the following that does not work as it is only looking at the first date specified and counting all marked with "A" if the first date matched the first quarter specifications.

{=(IF(($C$7:$C30>=$B$39)*($C$7:C$30<=$C$39),COUNTIF($L$7:$L30,"a"),0))}

Please help me figure out what is wrong with this formula that it will not look at each row individually and, is instead counting all indicated with A if the first date is found true against the if statement presented.

Sorry, not great at excel, any help is appreciated.

Thanks!

share|improve this question
add comment

1 Answer 1

I think you might have a problem with the way you've structured the array formula. Just unpacking your statement, you'll be building an array that will have as many rows as you have, with each row containing the the result of the COUNTIF($L$7:$L30,"a") if the date condition is met, or 0.

Since you're not applying any counting functions wrapping the whole lot up, the function will return the entire array but you're only going to see the first result in the cell, which is why it appears to be controlled purely by one cell.

An alternative way to approach this would be as follows:

Your three tests (for each row) are:

  • date >= B38
  • date <= C38
  • condition = "a"

and you only want to count where all three are true.

Handily enough, true values evaluate to 1, and false to 0, so we can use a multiply operation to get out what you want.

So, for a single row you'd want to do the following

(C7 >= B38) * (C7 <= C38) * (L7="a")

(and obviously, expanding it out to C7:C30 etc to cover your rows of interest)

To then collapse that into a single value, you then want to wrap the whole lot up in a SUM() statement which will just go through the list and add all the values together.

SUM( (C7:C30>B39) * (C7:C30<=C39) * (L7:L30="a") )

Also, just if you were curious to see, go back into your original formula, and wrap it up with a sum() statement, and you'll get the result showing as the number of times your status is set to "a", multiplied by the number of rows which met the date condition..

(noting: array formulas take a bit of head scratching initially, but once you get your head around them, they sorta work nicely. I originally read up on them at http://www.cpearson.com/excel/ArrayFormulas.aspx which is still a pretty good explanation if it's all still confusing..)

share|improve this answer
    
Thank you so much! –  user128953 Apr 18 '12 at 21:12
    
You could also replace SUM with SUMPRODUCT in tanantish's suggestion, then you don't require "array entry". Also if you have Excel 2007 or later you can use COUNTIFS for multiple conditions, i.e. =COUNTIFS(C$7:C$30,">="&B39,C$7:C$30,"<="&C39,L$7:L$30,"a") –  barry houdini Apr 18 '12 at 22:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.