countif and Vlookup

**sagar** · 02-11-2007, 07:50 AM

Hi,

Can any one help me,

Data "L4:Q12" values are taking through countif command from Apr-06. if I select next month through dropdown menu ("L2") values are to be updated for that month.

for more details see the attached file

Kind Regards
Sagar

**daddylonglegs** · 02-11-2007, 08:34 AM

Assuming all dates in column B will always show 1st of the relevant month, as per your example, try this formula in M5 copied across and down

=SUMPRODUCT(--($B$3:$B$55=$L$2),--(INDEX($C$3:$J$55,0,MATCH($L5,$C$2:$J$2,0)) =M$4&"Y"))

**sagar** · 02-11-2007, 10:04 AM

hi

Copied the same formula in "M5" cell and draged, but I am not getting the correct values (showing "0")

Please help me

Regards
Sagar

**VBA Noob** · 02-11-2007, 10:40 AM

It works

Just remove the excess space from

))=M$4&"Y "))

VBA Noob

**daddylonglegs** · 02-11-2007, 12:42 PM

Did you get it working? As VBA Noob says, a rogue space crept into my formula - sometimes happens when you post them. Here's an example with the working formula....

**sagar** · 02-11-2007, 12:43 PM

Thanks Noob,

I am very much intrested to know from where you people are solving the problems and few of them only replying to all the quiries, all are belongs to one team or operating from different places.

Regards
Sagar

**VBA Noob** · 02-11-2007, 12:48 PM

Hi sagar,

No teams. Just keen excel users I guess

I'm from the uk and try to answer posts and learn some VBA on the way

Still learning from DDL thou

VBA Noob

**sagar** · 02-12-2007, 12:46 PM

Hi Noob,

the formula is working fine, but I am facing problem with different date formats, an average monthly i will be receiveing the data from all the users (atleast @6000 entries), in that tems are not following one consistant date format, though it is formated as "Dec-06" they are entering with different dates like 12/5/06 or 12/7/06

is there any formula to change the the existing dates with month starting date (12/01/06) in the same cell.

Regards
Sagar

**daddylonglegs** · 02-12-2007, 12:51 PM

Try changing formula to

=SUMPRODUCT(--($B$3:$B$55-DAY($B$3:$B$55)+1=$L$2),--(INDEX($C$3:$J$55,0,MATCH($L5,$C$2:$J$2,0)) =M$4&"Y"))

**sagar** · 02-13-2007, 08:31 AM

Hi daddylonglegs,

Thanks for your help,

have good day
bye
sagar

**sagar** · 02-13-2007, 09:12 AM

Hi daddylonglegs,

One more help, in range L15:Q23 I want to coount how many 1LDY, 2LDY ......and till 8LDy are there for Apr, May and June month in the entire range (C3 : J55 range)

thanks
Sagar