INDIRECT & SUMPRODUCT QUESTION

[MESTRELLA29]

SUMPRODUCT(--(YEAR('Budget Expense'!$D$3:$D$10393)=$A$1),--('Budget
Expense'!$E$3:$E$10393=D$2),--('Budget Expense'!$J$3:$J$10393=$A19),'Budget
Expense'!$F$3:$F$10393)

This formula works well right now, How can I put INDIRECT function so i can
referance in Cell $D$3 Actual if we want to look at actuals sheet and Budget
if I want to look in budget Sheet.

 

[Dave Peterson]

=SUMPRODUCT(--(YEAR('Budget Expense'!$D$3:$D$10393)=$A$1),
--('Budget Expense'!$E$3:$E$10393=D$2),
--('Budget Expense'!$J$3:$J$10393=$A19),
'Budget Expense'!$F$3:$F$10393)

Becomes

=SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!$D$3:$D$10393")=$A$1),
--(indirect("'" & $d$3 & "'!$E$3:$E$10393")=D$2),
--(indirect("'" & $d$3 & "'!$J$3:$J$10393")=$A19),
indirect("'" & $d$3 & "'!$F$3:$F$10393")

 

[Bob Phillips]

=SUMPRODUCT(--(YEAR(INDIRECT("'"&D3&"'!$D$3:$D$10393"))=$A$1),
--(INDIRECT("'"&D3&"'!$E$3:$E$10393")=D$2),
--(INDIRECT("'"&D3&"'!$J$3:$J$10393")=$A19),INDIRECT("'"&D3&"'!$F$3:$F$10393"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ShaneDevenshire]

Hi,

Try this:

=SUMPRODUCT(--(YEAR(INDIRECT("'"&D1&"
Expense'!$D$3:$D$10393"))=$A$1),--INDIRECT("'"&D1&"
Expense'!$E$3:$E$10393")=D$2,--(INDIRECT("'"&D1&"
Expense'!$J$3:$J$10393")=$A19),INDIRECT("'"&D1&" Expense'!$F$3:$F$10393"))

 

[ShaneDevenshire]

Hi,

I did not point out the difference between my formula and the prior
solutions - you stated you wanted to type Budget into cell D3 so since the
sheet is named Budget Expense you need to include the text Expense in your
formula.

 

[MESTRELLA29]

For some reason this is not working, it is giving me a result but it is not
accurate,
Reult in This Formula is 586
=SUMPRODUCT(--(YEAR('Budget Expense'!$D$3:$D$10393)=$A$1),

--('Budget Expense'!$E$3:$E$10393=D$2),
--('Budget Expense'!$J$3:$J$10393=$A19),
'Budget Expense'!$F$3:$F$10393)
Becomes

Result for this formula is $1,112,594

 

[Dave Peterson]

And if you just hardcode the worksheet name into the formula, what's returned?

I'd bet that the formula is ok, but your data is either not numeric or not
matching the rows you think it should.

 

[MESTRELLA29]

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.

 

[T. Valko]

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 & "'!D310393"))=$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.

 

[T. Valko]

Ooops!

Typo:

.....and is in essence an absolute reference with needing the $ signs.

Should be:

......and is in essence an absolute reference without needing the $ signs.

 

[Dave Peterson]

We need to compare the two results of the two different versions of the formula.

What do you get when you use the =indirect() version and Budget in D3?
What do you get when you use the =indirect() version and Actual in D3?

If those variations of the formulas return the same as when you use the name,
then it's your data. Check for strings that look like numbers but are really
text.

 

[Dave Peterson]

Thanks for the correction, Biff.

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 & "'!D310393"))=$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.