Thread: Count and Sum on Multiple Criteria in Excel 2007

1. I'm trying to both count and sum (separately) in Excel 2007 where Range 1 = Value A and Range 2 < Value B. I've tried various permutations on SUMIF and SUMPRODUCT, including some of the helpful formulas elsewhere in the Lounge, but nothing seems to work. I'm using SUMPRODUCT elsewhere in the same workbook successfully. The difference seems to be that in the other places I'm going for Range 2 = Value B, but in this case Range 2 is dates, and I need <, <= and > than various values.

Can anybody spot why I'm not getting the right answers?

thanks

Don

2. One thing that comes to mind - how are you entering the date?
To use a date, if it's not a cell reference, you have to use something like this:
=sumproduct(--(a1:a10<--"4/1/10"))

if you trie to do it by just entering the date as 4/10/10, you'll be comparing the range to 4 divided by 10 divided by 10 or 0.04
=sumproduct(--(a1:a10< 4/1/10))

If this doesn't solve your problem, post a sample workbook. At the very least, please provide the formulas you've tried.

3. I may have figured it out.

This array: =SUMPRODUCT((Evaluation Range 1="Criteria 1")*(Evaluation Range 2<\$B403)*1) where \$B403 is a cell containing a date, seems to correctly count the number of cells meeting both Criteria 1 in Evaluation Range 1 and Criteria 2 in Evaluation Range 2.

This array: =SUMIFS(Sum Range,Evaluation Range 1,"=Criteria 1",Evaluation Range 2,"<"&\$B403) where \$B403 is a cell containing a date, seems to correctly add up the cells in the Sum Range where the both Criteria 1 in Evaluation Range 1 and Criteria 2 in Evaluation Range 2 are met.

Thanks for the quick response. I know I can always depend on the Lounge when I'm stuck on an Excel problem,

Don

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•