Countif Question

[carl]

Can the COUNTIF funtion count if the value in A1 matches
the first 7 characters of Column B

Thanks you in ADVANCE.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(A1:A100=LEFT(B1:B100))

 

[Peo Sjoblom]

SUMPRODUCT can

=SUMPRODUCT(--(LEFT(TRIM(B2:B30),7)=TRIM(A1)))

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[taylor]

you can try sum(if((A1="text")*(B1="text"),1))
This compares the A1 column against B1 column and for
every correct occurrence, it sums it. NOTE: this is the an
array function.

 

[Carl]

Hello Peo.

I cannot seem to get this too work. I think it may have
something to do with the formating and/or the fiact that I
am dealing with dates.

Would you be willing to take a look at a sample
spreadsheet ?

Regards.

 

[Peo Sjoblom]

It's better that you post a little text sample here
so everybody can benefit from it.
like
contents of A1


do you have a date in A1 and date and times in B?

=SUMPRODUCT(--(INT(B1:B30)=A1))

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[carl]

OK:
1 A B
2 Monitoring Statisitics 05/24/04
3 # Cancel Requests 5
4
5
6 Cancel Time
7 5/24/2004 1:30:10 PM
8 5/24/2004 1:30:10 PM
9 5/24/2004 1:30:10 PM

I had to place a ' infront of the entries in A7:A9 in
order to show you the contents of the cell. Without the ',
these cells look like:

A
6 Cancel Time
7 05/24/04
8 05/24/04
9 05/24/04

So I am trying to figure out a way to match the date in B2
with the "custom data" in Cells A7:A9.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(INT(A7:A9)=INT(B2)))