Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct & Date multiple criteria
I need to count items that equal a number(B3) range name is PHYNO, and = a
type "INPT" (range name is TYPE), and are greater than 1/1/2008(AH2). The date is in range name SURGDT and is formatted as *m/d/yyyy. When I try to do my formula, I get 0. This is the formula I've tried: =SUMPRODUCT((SURGDT=AH2)*(PHYNO=B3)*(TYPE="INPT") ) Hoping someone can help... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct & Date multiple criteria
Try this
=SUMPRODUCT((SURGDT=Date(year(AH2),month(AH2),day (AH2)))*(PHYNO=B3)*(TYPE="INPT")) -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Tasha" wrote: I need to count items that equal a number(B3) range name is PHYNO, and = a type "INPT" (range name is TYPE), and are greater than 1/1/2008(AH2). The date is in range name SURGDT and is formatted as *m/d/yyyy. When I try to do my formula, I get 0. This is the formula I've tried: =SUMPRODUCT((SURGDT=AH2)*(PHYNO=B3)*(TYPE="INPT") ) Hoping someone can help... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct & Date multiple criteria
Your formula works perfectly.
Make sure that each of your named ranges contain the same number of cells. Tom |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct & Date multiple criteria
Also,
The named range cannot be an entire column (e.g. "A:A") but must include rows (e.g. "A1:A65536" or "B1:B12"). Tom |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct & Date multiple criteria
Thanks Barb!! I used your formula, but now am getting #N/A!. Also, should
have mentioned, my range names are a formula so they are dynamic, which is as follows: PHYNO =OFFSET(all07_08!$F$2,0,0,COUNTA(all07_08!$F:$F),1 ) SURGDT =OFFSET(all07_08!$A$2,0,0,COUNTA(all07_08!$A:$A),1 ) TYPE =OFFSET(all07_08!$C$2,0,0,COUNTA(all07_08!$C:$C),1 ) "Barb Reinhardt" wrote: Try this =SUMPRODUCT((SURGDT=Date(year(AH2),month(AH2),day (AH2)))*(PHYNO=B3)*(TYPE="INPT")) -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Tasha" wrote: I need to count items that equal a number(B3) range name is PHYNO, and = a type "INPT" (range name is TYPE), and are greater than 1/1/2008(AH2). The date is in range name SURGDT and is formatted as *m/d/yyyy. When I try to do my formula, I get 0. This is the formula I've tried: =SUMPRODUCT((SURGDT=AH2)*(PHYNO=B3)*(TYPE="INPT") ) Hoping someone can help... |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct & Date multiple criteria
I used Barb's formula, but now am getting #N/A!. Also, should have
mentioned, my range names are a formula so they are dynamic, which is as follows: PHYNO =OFFSET(all07_08!$F$2,0,0,COUNTA(all07_08!$F:$F),1 ) SURGDT =OFFSET(all07_08!$A$2,0,0,COUNTA(all07_08!$A:$A),1 ) TYPE =OFFSET(all07_08!$C$2,0,0,COUNTA(all07_08!$C:$C),1 ) "TomPl" wrote: Your formula works perfectly. Make sure that each of your named ranges contain the same number of cells. Tom |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct & Date multiple criteria
I tested again and find that it work fine on the condition that each of your
ranges is the same size. If columns A, C and F have data in exactly the same rows it works. If data is missing from one cell you get unequal range sizes and the formula does not work. If you know that one column will contain a complete set of data while the other two don't you might change your formula: Assume SURGDT will always have a date even though there may be blanks in PHYNO and TYPE. Consider these range names: PHYNO =OFFSET(all07_08!$F$2,0,0,COUNTA(all07_08!$A:$A),1 ) SURGDT =OFFSET(all07_08!$A$2,0,0,COUNTA(all07_08!$A:$A),1 ) TYPE =OFFSET(all07_08!$C$2,0,0,COUNTA(all07_08!$A:$A),1 ) I haven't tested this but I think it should work. This is a rather fragile setup you have. Tom |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct & Date multiple criteria
Yup, that worked based on the assumption that there is a date in column A
without any skipped rows. I think this would be more solid logic if you could use: PHYNO =all07_08!$F$2:$F65535 SURGDT =all07_08!$A$2:$A65535 TYPE =all07_08!$C$2:$C65535 I hope that helps. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct & Date multiple criteria
Thanks!! I changed my range names to the way you showed it and it is working
now. Thanks so much!!! "TomPl" wrote: Yup, that worked based on the assumption that there is a date in column A without any skipped rows. I think this would be more solid logic if you could use: PHYNO =all07_08!$F$2:$F65535 SURGDT =all07_08!$A$2:$A65535 TYPE =all07_08!$C$2:$C65535 I hope that helps. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple criteria for sumproduct | Excel Discussion (Misc queries) | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
HELP...SUMPRODUCT with multiple criteria | Excel Discussion (Misc queries) | |||
Sumproduct multiple criteria | Excel Worksheet Functions | |||
multiple criteria in if or sumproduct | Excel Worksheet Functions |