IF ISNUMBER LVOOKUP formula

[ved2011]

Hello,

I am trying to create a formula where if a date is shown in cell A4, in Cell B4 it will show the value of a formula on another sheet. Based on the date in cell A4 i want the cell to show the formula that calculated the summation for that date in cell B4.

Currently I have where the date is 5/3/2011

=(ISNUMBER(A4),VLOOKUP(TAB!A2))

on sheet Tab in cell A2 I have the equation

=SUMPRODUCT(--(TCC!A3:A500=DATEVALUE("5/3/2011")),TCC!B3:B500)

this is calculating the total number of items sold on 5/3/2011 from multiple sales reps.

The second equation is working fine its the first one that is having trouble. Any thoughts?? would an index match equation work better? thanks

 

[JoeMo]

If you just want the value that's in A2 on the sheet 'TAB' try:

=IF(ISNUMBER(A4),TAB!A2,"")

 

[ved2011]

Yes that worked perfectly thank you so much!!! One other question. Lets say I wanted to do that same thing for more than one date.

I want that whatever date I entered into cell A4 (from 5/1/2011 to 5/31/2011) to be associated with that same formula from the tab sheet but i want it to correspond to the correct date in each instance.

=SUMPRODUCT(--(TCC!A3:A500=DATEVALUE("5/3/2011")),TCC!B3:B500)

so for 5/4/2011 in cell A4 i want the formula
=SUMPRODUCT(--(TCC!A3:A500=DATEVALUE("5/4/2011")),TCC!B3:B500) to calculate automatically from my tab sheet.

I do not know if there is a way to do the date value as =DATEVALUE"5/3/2011","5/4/2011","5/5/2011"... meaning to sum the product if it is one of these dates with in B3 and B500 in sheet TCC.

Any thoughts would be a great help. Thanks so much!!! and thanks again for the previous answer!

 

[T. Valko]

Yes that worked perfectly thank you so much!!! One other question. Lets say I wanted to do that same thing for more than one date.

I want that whatever date I entered into cell A4 (from 5/1/2011 to 5/31/2011) to be associated with that same formula from the tab sheet but i want it to correspond to the correct date in each instance.

=SUMPRODUCT(--(TCC!A3:A500=DATEVALUE("5/3/2011")),TCC!B3:B500)

so for 5/4/2011 in cell A4 i want the formula
=SUMPRODUCT(--(TCC!A3:A500=DATEVALUE("5/4/2011")),TCC!B3:B500) to calculate automatically from my tab sheet.

I do not know if there is a way to do the date value as =DATEVALUE"5/3/2011","5/4/2011","5/5/2011"... meaning to sum the product if it is one of these dates with in B3 and B500 in sheet TCC.

Any thoughts would be a great help. Thanks so much!!! and thanks again for the previous answer!

Use a cell to hold the date criteria:

• A1 = some date like 5/3/2011

Then:

=SUMIF(TCC!A3:A500,A1,TCC!B3:B500)