COUNTIF - Three criteria?

  • Thread starter Thread starter Alden
  • Start date Start date
A

Alden

I am trying to average cells based on cells on the same row, that fit within
two criteria (dates), from another colum.

Here is a less complicated version of what I have now, without crazy
references.

=SUMPRODUCT(A4:A63>=G3)*(A4:A63<=H3)*(BN4:BN63))
Range A4:63 are dates, as are cells G3 and H3 that I use for the criteria.

How can I count the non blank cells, that also are on the same row as cells
that are within the criteria in G3 and H3? (Dates) Right now this formula
only sums.. and I need an average. I figured if I can get an accurate count
that matches the dates, I could just divide.


This is the actual formula... but could be quite confusing with the
referencing to another sheet. I input data into named cells and can change
the sheet, and workbook name at will, and ge tthe data as long as the correct
workbook is open.
=SUMPRODUCT(((INDIRECT("'["&Scorecard&"]"&$B10&"'!"&Dates))>=G3)*(((INDIRECT("'["&Scorecard&"]"&$B10&"'!"&Dates))<=H3)*((INDIRECT("'["&Scorecard&"]"&$B10&"'!"&AHT)))))

Thank you for taking the time to check this out for me!
 
Referring to your first formula, this amendment will allow you to
count the number of non-blank cells that fit within the dates:

=SUMPRODUCT((A4:A63>=G3)*(A4:A63<=H3)*(BN4:BN63<>""))

Just use this as the divisor to obtain the average.

Hope this helps.

Pete
 
That did the trick, thank you!!!

Pete_UK said:
Referring to your first formula, this amendment will allow you to
count the number of non-blank cells that fit within the dates:

=SUMPRODUCT((A4:A63>=G3)*(A4:A63<=H3)*(BN4:BN63<>""))

Just use this as the divisor to obtain the average.

Hope this helps.

Pete


I am trying to average cells based on cells on the same row, that fit within
two criteria (dates), from another colum.

Here is a less complicated version of what I have now, without crazy
references.

=SUMPRODUCT(A4:A63>=G3)*(A4:A63<=H3)*(BN4:BN63))
Range A4:63 are dates, as are cells G3 and H3 that I use for the criteria.

How can I count the non blank cells, that also are on the same row as cells
that are within the criteria in G3 and H3? (Dates) Right now this formula
only sums.. and I need an average. I figured if I can get an accurate count
that matches the dates, I could just divide.

This is the actual formula... but could be quite confusing with the
referencing to another sheet. I input data into named cells and can change
the sheet, and workbook name at will, and ge tthe data as long as the correct
workbook is open.
=SUMPRODUCT(((INDIRECT("'["&Scorecard&"]"&$B10&"'!"&Dates))>=G3)*(((INDIREC­T("'["&Scorecard&"]"&$B10&"'!"&Dates))<=H3)*((INDIRECT("'["&Scorecard&"]"&$­B10&"'!"&AHT)))))

Thank you for taking the time to check this out for me!
Click to expand...
Click to expand...
 
Back
Top