Formula Help

N

nic17

Hi Guys,

I need help with a formula.

I want to transfer this text into a formula:

Count the number of cells in column K that have a date of october, an
place this result in cell C6.

Is this possible
 
F

Freemini

Probably the easiest way to do this is to create a helper column in co
L with the formula =Month(K1) in cell L1, copy this down to the end o
your data.

In cell C6 enter the formula =countif(L:L,10)

hth

Mik
 
D

Domenic

Hi,

Try,

=SUMPRODUCT(--(MONTH(K1:K10)=10),--(YEAR(K1:K10)=2004))

Hope this helps!
 
N

nic17

Not really possible to insert another column, as its is a continuou
report pulled by a macro.

Is their a way to just use the current date column? Would somethin
like this work?

=countif(K:K,1:31/10/2003
 
D

Domenic

nic17 said:
Is their a way to just use the current date column? Would something
like this work?

=countif(K:K,1:31/10/2003)
Click to expand...

You could try something like this:

=COUNTIF(K:K,">="&--"10/1/2004")-COUNTIF(K:K,">"&--"10/31/2004")

So I take it that you didn't particularly like my earlier suggestion:

=SUMPRODUCT(--(MONTH(K1:K100)=10),--(YEAR(K1:K100)=2004))
 
N

nic17

Thanks,

I've tried this, but it doesn't seem to work.

My date column is in the following format dd/mm/yyyy.

Any ideas
 
N

nic17

Apologies, this does work, thanks.

My next step now!

I have another column - Ord. Val Ex VAT.

I want to total the cells in this column, but only those cells tha
have a date of october!

Any ideas
 
N

nic17

Yes, that worked fine, thank you.

I have a report, from which i need to pull the number of orders from
any particular month, which your formula now does, thanks.

But i also need to calculate the value of these orders for a particular
month.

The date is in column K and the value in column U.

I need to calculate the sum of all orders for a particular month.

E.g. Their are 166 orders for October (The data source lists the past
12 months of orders) and i need the value of the sum of all the 166
orders for October.
 
N

nic17

I have a report, from which i need to pull the number of orders from an
particular month, which your formula now does, thanks.

But i also need to calculate the value of these orders for a particula
month.

The date is in column K and the value in column U.

I need to calculate the sum of all orders for a particular month.

E.g. Their are 166 orders for October (The data source lists the pas
12 months of orders) and i need the value of the sum of all the 16
orders for October.

Would something like this work?

=SUM((U:U)IF(K:K,"*10/2004*")
 
N

nic17

Sorry, this formula worked for me:

=SUMPRODUCT(--(MONTH(K2:K40000)=10),--(YEAR(K2:K40000)=2003))

This reported that their were 166 records with a date of October, whic
is correct.

Now i need to calculate the total value of these 166 records. With th
values being in column 'U'
 
D

Domenic

Try,

=SUMPRODUCT((RIGHT(K1:K1000,7)="10/2004")*(U1:U1000))

Adjust the ranges accordingly.
 
N

nic17

Thanks,

I've tried, but returns a value of '0' which isn't right.

This is difficult!

Any more ideas? I will keep trying
 
Top