Thanks Biff
I finally saw the mistake myself
Thanks for the patience
"Biff" wrote:
Ok, you have mismatched range sizes. The ranges must be the EXACT same size:
=SUMPRODUCT(--('Cattle Mvts'!E29:'Cattle Mvts'!E42='Cattle
Budget'!E7),('Cattle Mvts'!C29:'Cattle Mvts'!C41)*'Feedlot
Assumptions'!B5)
'Cattle Mvts'!E29:'Cattle Mvts'!E42
'Cattle Mvts'!C29:'Cattle Mvts'!C41
Those are not the same size! You either have to change E42 to E41 or, C41 to
C42
Also, it's not necessary to repeat the sheet name:
'Cattle Mvts'!E29:'Cattle Mvts'!E42
'Cattle Mvts'!E29:E42
Note! Exact range sizes means the exact same length or width:
A1:A5 and B2:B6 are the exact same size!
Also, you don't need the extra ( ):
........),('Cattle Mvts'!C29:'Cattle Mvts'!C41)*'Feedlot Assumptions'!B5)
Should be:
......),'Cattle Mvts'!C29:C41)*'Feedlot Assumptions'!B5
Biff
"Anthony" wrote in message
...
Sorry bout delay in reply, i went to bed for a while.
Exact formula is
=SUMPRODUCT(--('Cattle Mvts'!E29:'Cattle Mvts'!E42='Cattle
Budget'!E7),('Cattle Mvts'!C29:'Cattle Mvts'!C41)*'Feedlot
Assumptions'!B5)
A B C D E
28 Purchases # $/Hd Mth
29 Feeder Steers 100 412.5 Jul-05
30 Feeder Steers 34 412.5 Aug-06
31
32
33
34
checked to make sure formats are correct.
anthony
"Biff" wrote:
Hi!
What is the *exact* formula that you used?
could it be that E29 to E41 and d7 are all dates showing as Jul 05 etc
That should not make any difference. If they were all dates or if they
were
all just text strings that would not make a difference. If D7 was a text
string and E29:E41 were true dates or vice versa, the formula would
return
an incorrect result but not a #VALUE! error.
Are the values in C29:C41 and B5 really numbers?
The only things that will cause a #VALUE! error are if the values in
C29:C41
or B5 are really TEXT or if the range sizes are not the same.
Biff
"Anthony" wrote in message
...
could it be that E29 to E41 and d7 are all dates showing as Jul 05 etc
"Biff" wrote:
Hi!
Try this:
=SUMPRODUCT(--('Cattle Mvts'!E29:E41='Cattle Budget'!D7),'Cattle
Mvts'!C29:C41)*'Feedlot Assumptions'!B5
Biff
"Anthony" wrote in message
...
May not have explained my problem clearly enough so here it is
again,
hopefully someone is able to help.The formula below works well for a
set
scenario
=IF('Cattle Budget'!D7='Cattle Mvts'!E29,'Cattle Mvts'!C29*'Feedlot
Assumptions'!B5,0)
however what I now need is that if the date in D7 is equal to the
date
showing in any of E29 to E41 then multiply the corresponding row in
the
C
column by B5, and add them all togther.
Hopefully someone can help
thanks
anthony
|