Countif Multiple Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi-

The following array formula is working for me:
=(SUMPRODUCT(--(D9:D55>DATEVALUE("12/31/2006")),(--(D9:D55<=K1)),E9:E55))/(COUNTIF(D9:D55,">12/31/2006")-COUNTIF(D9:D55,">5/31/2007")).

However I would like for 5/31/2007 to be a cell reference of K1. I want to
be able to change just K1 each month. I can not get this to work with a cell
reference. Column D has dates and Column E is numbers.

Thanks again.
 
=SUMPRODUCT(--(D9:D55>DATEVALUE("12/31/2006")),--(D9:D55<=K1),E9:E55)/(COUNTIF(D9:D55,">12/31/2006")-COUNTIF(D9:D55,K1))
 
You didn't say what formula you tried. Was it
=(SUMPRODUCT(--(D9:D55>DATEVALUE("12/31/2006")),(--(D9:D55<=K1)),E9:E55))/(COUNTIF(D9:D55,">12/31/2006")-COUNTIF(D9:D55,">"&K1))
?
 
I accidentally responded direct on this. So this is a re-response

I'm not sure exactly what this formula is supposed to accomplish, but see if
this helps.

=(SUMPRODUCT(--(D9:D55>DATEVALUE("12/31/2006")),(--(D9:D55<=VALUE(K1))),E9:E55))/(COUNTIF(D9:D55,">12/31/2006")-COUNTIF(D9:D55,">"
& VALUE(K1)))

HTH,
Paul
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combining Functions (Sumproduct and Countif) 5
multiple IF calculations 6
SumProduct + cells 3
end result 4
Indirect inside a vlookup 3
Is there a way to find a value in a set of cells? 3
How can find a value using two different matching criteria? 6
DateRange Lookup no exact match, can't use lesser 4

Back
Top