# Formula complications

#### LozSale

##### Board Regular
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:

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Is H3 formatted as a date?

LozSale said:
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).

Thanks guys,

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

LozSale said:
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))

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.

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

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

=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))

Replies
11
Views
431
Replies
1
Views
163
Replies
3
Views
122
Replies
6
Views
201
Replies
6
Views
280

1,203,171
Messages
6,053,885
Members
444,692
Latest member
Queendom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back