Problem with an "if" relating to a range

[Guest]

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

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--('Cattle Mvts'!E29:E41='Cattle Budget'!D7),'Cattle
Mvts'!C29:C41)*'Feedlot Assumptions'!B5

Biff

 

[Guest]

Getting a #value error coming up

Any ideas

anthony

 

[Guest]

could it be that E29 to E41 and d7 are all dates showing as Jul 05 etc

 

[Biff]

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

 

[Guest]

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]

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

 

[Guest]

Thanks Biff

I finally saw the mistake myself

Thanks for the patience