Formula complications

[LozSale]

Can anyone help?????

Please look at the following formula:

=SUMPRODUCT(--(LG!$Q$3:$Q$39="05 Sax"),--(LG!$P$3:$P$39>=TODAY()),--(LG!$P$3:$P$39<=$H$3))

This refers to one of a number of cells that form a consolidation sheet for a 23 sheet file. I am asking to look at sheet 'LG' and within that sheet seek out all instances of '05 Sax' in the selection $Q$3:$Q$39. Of those return the number of instances with a date between TODAY and $H$3. (H3 is currently 01/04/07 but varies)

For some reason this forumla returns a '0' when I know that is should be 1.

I would be grateful for any help.

Many thanks

Loz :wink:

 

[NBVC]

Is H3 formatted as a date?

 

[Barrie Davidson]

For some reason this forumla returns a '0' when I know that is should be 1.

What is the value in column P for the record you know should return a value of 1?

And...are you sure that the value in column Q is "05 Sax" and not something like "05 Sax " (note the additional space).

 

[LozSale]

Thanks guys,

H3 is formatted as date (ddmmyy) and the values in column P are also formatted dates.

 

[Barrie Davidson]

What about my questions??

 

[Aladin Akyurek]

Thanks guys,

H3 is formatted as date (ddmmyy) and the values in column P are also formatted dates.

What do you get with:

1.

=SUMPRODUCT(--(TRIM(LG!$Q$3:$Q$39)="05 Sax"),--(LG!$P$3:$P$39>=TODAY()),--(LG!$P$3:$P$39<=$H$3))

2.

=SUMPRODUCT(--(TRIM(LG!$Q$3:$Q$39)="05 Sax"),--(LG!$P$3:$P$39+0>=TODAY()),--(LG!$P$3:$P$39+0<=$H$3))

 

[LozSale]

Sorry Barry,

The P column displays formatted dates and there were no spacing errors within the original data.

The suggested forumla also returned '0'.

Here is a snap shot of the source table that feeds the consolidation sheet.

*Removed by Admin. Refer to http://www.mrexcel.com/board2/viewtopic.php?t=92622 for help posting sheet data.

 

[Barrie Davidson]

Looks to me like column P doesn't contain dates. How is 130613 a date?

 

[LozSale]

As I said earlier, the date format is ddmmyy so 160613 is 16th June 2013.

 

[Barrie Davidson]

How about?

=SUMPRODUCT(--(LG!$Q$3:$Q$39="05 Sax"),--(LG!$P$3:$P$39>=TEXT(TODAY(),"ddmmyy")+0),--(LG!$P$3:$P$39<=TEXT($H$3,"mmddyy")+0))