Need help with a formula

[SOULKAGE]

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.

 

[xld]

Try

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

 

[Aladin Akyurek]

Try...

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

Hi SOULKAGE,

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

Try this:

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

 

[SOULKAGE]

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]

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

Again, Thank you very much for your help with this