Hi...

I want to count products (screws) sold in Jan on a live rolling table
without creating another table. I want to use countif to work against the
screw value AND a date value simoutaneously. Is this possible?

Thanks

Gordon...

Have a look at SUMPRODUCT function!
Regards,
Stefi


€žGordon€� ezt Ã*rta:

Hi...

I want to count products (screws) sold in Jan on a live rolling table
without creating another table. I want to use countif to work against the
screw value AND a date value simoutaneously. Is this possible?

Thanks

Gordon...

Hi Stefi...

Tried this and it doesn't quite fit. To explain further, In column A random
products are added as text, screws, nails, hammers etc and adjacant to this
in column B is a month value 1-12. I need to know on a rolling basis how many
of each tool were sold on any given month. I have a small seperate table that
lists the tools, and then cells for each month. I normally right endless
=if(and( columns but there must be an easier way?

Any assistance appreciated.

Gordon...

"Stefi" wrote:

Have a look at SUMPRODUCT function!
Regards,
Stefi


€žGordon€� ezt Ã*rta:

Hi...

I want to count products (screws) sold in Jan on a live rolling table
without creating another table. I want to use countif to work against the
screw value AND a date value simoutaneously. Is this possible?

Thanks

Gordon...

I have posted an example at http://cjoint.com/?icl2ypomXW

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gordon" wrote in message
...
Hi Stefi...

Tried this and it doesn't quite fit. To explain further, In column A
random
products are added as text, screws, nails, hammers etc and adjacant to
this
in column B is a month value 1-12. I need to know on a rolling basis how
many
of each tool were sold on any given month. I have a small seperate table
that
lists the tools, and then cells for each month. I normally right endless
=if(and( columns but there must be an easier way?

Any assistance appreciated.

Gordon...

"Stefi" wrote:

Have a look at SUMPRODUCT function!
Regards,
Stefi


"Gordon" ezt írta:

Hi...

I want to count products (screws) sold in Jan on a live rolling table
without creating another table. I want to use countif to work against
the
screw value AND a date value simoutaneously. Is this possible?

Thanks

Gordon...

Hi Gordon,

Sheet1:
A B C
Product name Month Quantity sold
screws 1 1
nails 1 2
hammers 1 3
....

Sheet2:
A B C D E F G H I J K L M
Product name 1 2 3 4 5 6 7 8 9 10 11 12
screws *
nails
hammers


Formula in B2 (marked with *):
=SUMPRODUCT(--(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)=$A2),--(OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$A:$A)-1)=B$1),OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$A:$A )-1))

Regards,
Stefi


Drag it to right
€žGordon€� ezt Ã*rta:

Hi Stefi...

Tried this and it doesn't quite fit. To explain further, In column A random
products are added as text, screws, nails, hammers etc and adjacant to this
in column B is a month value 1-12. I need to know on a rolling basis how many
of each tool were sold on any given month. I have a small seperate table that
lists the tools, and then cells for each month. I normally right endless
=if(and( columns but there must be an easier way?

Any assistance appreciated.

Gordon...

"Stefi" wrote:

Have a look at SUMPRODUCT function!
Regards,
Stefi


€žGordon€� ezt Ã*rta:

Hi...

I want to count products (screws) sold in Jan on a live rolling table
without creating another table. I want to use countif to work against the
screw value AND a date value simoutaneously. Is this possible?

Thanks

Gordon...

Hi Bob...

The example that you kindly provided contained code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
'do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Since I intergrated this sheet into

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Was this code relavent to the formula example. Also, since I've added the
example you provided the sheet has stopped auto calculating even though the
option is ticked. Is that issue connected someway to the code above. It's a
bit beyond my level of expertese.

Thanks

Gordon...



"Bob Phillips" wrote:

I have posted an example at http://cjoint.com/?icl2ypomXW

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gordon" wrote in message
...
Hi Stefi...

Tried this and it doesn't quite fit. To explain further, In column A
random
products are added as text, screws, nails, hammers etc and adjacant to
this
in column B is a month value 1-12. I need to know on a rolling basis how
many
of each tool were sold on any given month. I have a small seperate table
that
lists the tools, and then cells for each month. I normally right endless
=if(and( columns but there must be an easier way?

Any assistance appreciated.

Gordon...

"Stefi" wrote:

Have a look at SUMPRODUCT function!
Regards,
Stefi


"Gordon" ezt Ã*rta:

Hi...

I want to count products (screws) sold in Jan on a live rolling table
without creating another table. I want to use countif to work against
the
screw value AND a date value simoutaneously. Is this possible?

Thanks

Gordon...

Gordon,

The code is not necessary, it is code that I have in my standard workbook, I
forgot to delete it. There will also be code behind all sheets and
ThisWorkbook. none of which is required, just the formulae.

Get rid of the code and see if it autocalcaulates then, it should do.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gordon" wrote in message
...
Hi Bob...

The example that you kindly provided contained code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
'do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Since I intergrated this sheet into

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Was this code relavent to the formula example. Also, since I've added the
example you provided the sheet has stopped auto calculating even though
the
option is ticked. Is that issue connected someway to the code above. It's
a
bit beyond my level of expertese.

Thanks

Gordon...



"Bob Phillips" wrote:

I have posted an example at http://cjoint.com/?icl2ypomXW

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gordon" wrote in message
...
Hi Stefi...

Tried this and it doesn't quite fit. To explain further, In column A
random
products are added as text, screws, nails, hammers etc and adjacant to
this
in column B is a month value 1-12. I need to know on a rolling basis
how
many
of each tool were sold on any given month. I have a small seperate
table
that
lists the tools, and then cells for each month. I normally right
endless
=if(and( columns but there must be an easier way?

Any assistance appreciated.

Gordon...

"Stefi" wrote:

Have a look at SUMPRODUCT function!
Regards,
Stefi


"Gordon" ezt írta:

Hi...

I want to count products (screws) sold in Jan on a live rolling
table
without creating another table. I want to use countif to work
against
the
screw value AND a date value simoutaneously. Is this possible?

Thanks

Gordon...