# Need help with a formula

#### SOULKAGE

##### New Member
Hi all,

Been having a spot of bother with getting a formula to work right.

Basically i need it to do this,

=SUMPRODUCT((Pending!G2:G6000>=A4)*(Pending!G2:G6000<A5))

which works fine in another column, aswell as this

=COUNTIF(Pending!E1:E6000,"*A8*")

which also works fine. I cant seem to get the two to combine or find an alternative to what im trying to do which is get a range of dates between two points in coloumn G (lets say 15/01/2009 and 22/01/2009) and from that selection tell me how many of them rows contain the phrase "A8" in coloumn E. Iv tryed many different formulas and arrays but cant seems to get it right.

Any help would be much appreciated.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### xld

##### Banned
Try

=SUMPRODUCT((Pending!G2:G6000>=A4)*(Pending!G2:G6000<A5)*(ISNUMBER(SEARCH("A8",Pending!E2:E6000))))

##### MrExcel MVP
Try...
Code:
``````=SUMPRODUCT(
--(Pending!G2:G6000>=A4),
--(Pending!G2:G6000<=B4),
--ISNUMBER(SEARCH("A8",Pending!E1:E6000)))``````

A4:B4 houses dates like 15/01/2009 and 22/01/2009.

#### Colin Legg

##### MrExcel MVP
Hi SOULKAGE,

Your formula got cut off so I'll assume the other date was in A5.

Try this:

Code:
``````=SUMPRODUCT(
--(Pending!G2:G6000 >= A4),
--(Pending!G2:G6000 <= A5),
--ISNUMBER(SEARCH("A8",Pending!E2:E6000)))``````

#### SOULKAGE

##### New Member
That works brilliantly thank you.

Just one more question. In column E where it is counting up the cells that contain "A8" there is other information, often in the same cell. How can this count up the cells that have "A8" in them aswell as other data? I tryed altering it to "*A8*" as with the COUNTIF but that didnt change the results?

#### SOULKAGE

##### New Member
Ignore that last question, i had an auto filter on. Silly me.

Again, Thank you very much for your help with this

Replies
3
Views
148
Replies
5
Views
367
Replies
17
Views
322
Replies
16
Views
1K
Replies
12
Views
319

1,191,170
Messages
5,985,066
Members
439,938
Latest member
MAlhash

### 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