Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF - Three criteria?
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF - Three criteria?
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 On Apr 30, 3:27*pm, Alden wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF - Three criteria?
That did the trick, thank you!!!
"Pete_UK" wrote: 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 On Apr 30, 3:27 pm, Alden wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF - Three criteria?
You're welcome - thanks for feeding back.
Pete On Apr 30, 6:01*pm, Alden wrote: That did the trick, thank you!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Countif two criteria | Excel Discussion (Misc queries) | |||
Countif with more than one criteria | Excel Discussion (Misc queries) | |||
countif but have two criteria | New Users to Excel | |||
Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions |