| Home |
| Search |
| Today's Posts |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT & SUMPRODUCT QUESTION
Thanks for the correction, Biff.
"T. Valko" wrote: There's a missing ")" in the INDIRECT version for the YEAR function: =SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!$D$3:$D$10393")=$A$1), Should be: =SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!$D$3:$D$10393"))=$A$1), Also, you can save a few keystrokes by eliminating the $ signs in the array reference of INDIRECT: =SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!D3:D10393"))=$A$1), Since INDIRECT evaluates this as a TEXT string the references will not change if copied and is in essence an absolute reference with needing the $ signs. -- Biff Microsoft Excel MVP "MESTRELLA29" wrote in message ... Ok maybe this can help out. I change the Name of the Sheet to Actual and Budget =SUMPRODUCT(--(YEAR(Budget!$D$3:$D$10000)=$A$1),--(Budget!$E$3:$E$10000=D$2),--(Budget!$J$3:$J$10000=$A37),Budget!$F$3:$F$10000) This Need to = 586 and equals = $1,112,594 =SUMPRODUCT(--(YEAR(Actual!$D$3:$D$10000)=$A$1),--(Actual!$E$3:$E$10000=D$2),--(Actual!$J$3:$J$10000=$A37),Actual!$F$3:$F$10000) This Need to = 1.40 and equals = 2671 I see the formula and it should worke but I do not understand why it still wrong, now the totals for $F3:$F10000 in Budget is 94500 only not 1,112,594 so I do not know where is this getting the data. -- Dave Peterson |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct with indirect | Excel Worksheet Functions | |||
| SUMPRODUCT & INDIRECT? | Excel Worksheet Functions | |||
| Need help with using SUMPRODUCT with INDIRECT | Excel Worksheet Functions | |||
| Help with Sumproduct with Indirect | Excel Worksheet Functions | |||
| sumproduct & indirect | Excel Worksheet Functions | |||
Threaded Mode
