Hi All,

I have the following data:

Product Month
A 1
B 1
C 1
A 2
B 3
A 4
B 7
C 2
D 3
B 1
C 2
D 9

Could you please provide me the formula to count A, B, C, and D with below
conditions:
<2 months 2 - 6 months 6 months
A count= ? ? ?
B ? ? ?
C ? ? ?
D ? ? ?

Thanks & regards,

Ryan L.,

See the attached workbook "SUMPRODUCT - Count formula - Ryan L -
SDG.xls".


Have a great day,
Stan


+-------------------------------------------------------------------+
|Filename: SUMPRODUCT - Count formula - Ryan L - SDG.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=176|
+-------------------------------------------------------------------+

--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: http://www.thecodecage.com/forumz/member.php?userid=503
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115660

In order to use a single formula that can be copied you need to create some
meaningful column headers that can be referenced by the formula.

I assume that month is the month number where 1 = Jan and 12 = Dec.

Let's assume your data is in the range A2:B13 and there are no empty cells
in B2::B13.

Enter these column headers for the table:

E1:G1 = 0,2,7
E2:G2 = 1,6,12

D3:D6 = A,B,C,D

Enter this formula in E3:

=SUMPRODUCT(--($A$2:$A$13=$D3),--($B$2:$B$13=E$1),--($B$2:$B$13<=E$2))

Copy across to G3 then down to E6:G6.

--
Biff
Microsoft Excel MVP


"Ryan L." <Ryan wrote in message
...
Hi All,

I have the following data:

Product Month
A 1
B 1
C 1
A 2
B 3
A 4
B 7
C 2
D 3
B 1
C 2
D 9

Could you please provide me the formula to count A, B, C, and D with below
conditions:
<2 months 2 - 6 months 6 months
A count= ? ? ?
B ? ? ?
C ? ? ?
D ? ? ?

Thanks & regards,

Few thoughts..

--Do you have a space at the beginning ot the cell content or a Tab which
force the data to the next line.
--Does FormatCellsAlignmentText Alignment help..

If this post helps click Yes
---------------
Jacob Skaria


"Ryan L." wrote:

Hi All,

I have the following data:

Product Month
A 1
B 1
C 1
A 2
B 3
A 4
B 7
C 2
D 3
B 1
C 2
D 9

Could you please provide me the formula to count A, B, C, and D with below
conditions:
<2 months 2 - 6 months 6 months
A count= ? ? ?
B ? ? ?
C ? ? ?
D ? ? ?

Thanks & regards,

Sorry guys, please ignore the previous post; it was meant for another thread.

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Few thoughts..

--Do you have a space at the beginning ot the cell content or a Tab which
force the data to the next line.
--Does FormatCellsAlignmentText Alignment help..

If this post helps click Yes
---------------
Jacob Skaria


"Ryan L." wrote:

Hi All,

I have the following data:

Product Month
A 1
B 1
C 1
A 2
B 3
A 4
B 7
C 2
D 3
B 1
C 2
D 9

Could you please provide me the formula to count A, B, C, and D with below
conditions:
<2 months 2 - 6 months 6 months
A count= ? ? ?
B ? ? ?
C ? ? ?
D ? ? ?

Thanks & regards,

Ryan L.

I think , you can use sumif for your request

for example
if Product is in column A and Month is in column B you should write your
formula in column c :
=SUMIF($A$1:$A$12;A1;$B$1:$B$12)

now , you can see your answer

Hi Biff,

Could you please explain why you have the value 0,2,7, 1,6, 12 in column
headers

Thanks & regards,

"T. Valko" wrote:

In order to use a single formula that can be copied you need to create some
meaningful column headers that can be referenced by the formula.

I assume that month is the month number where 1 = Jan and 12 = Dec.

Let's assume your data is in the range A2:B13 and there are no empty cells
in B2::B13.

Enter these column headers for the table:

E1:G1 = 0,2,7
E2:G2 = 1,6,12

D3:D6 = A,B,C,D

Enter this formula in E3:

=SUMPRODUCT(--($A$2:$A$13=$D3),--($B$2:$B$13=E$1),--($B$2:$B$13<=E$2))

Copy across to G3 then down to E6:G6.

--
Biff
Microsoft Excel MVP


"Ryan L." <Ryan wrote in message
...
Hi All,

I have the following data:

Product Month
A 1
B 1
C 1
A 2
B 3
A 4
B 7
C 2
D 3
B 1
C 2
D 9

Could you please provide me the formula to count A, B, C, and D with below
conditions:
<2 months 2 - 6 months 6 months
A count= ? ? ?
B ? ? ?
C ? ? ?
D ? ? ?

Thanks & regards,

The column headers a

0...2...7
1...6...12

You said these are your conditions:

<2 months 2 - 6 months 6 months

Less than 2 months
From 2 to 6 months
Greater than 6 months

In order to use a *single* formula that can be copied you need to translate
these conditions:

<2 months 2 - 6 months 6 months

Into something that can be used in a formula. That's what my suggested
headers do. Since the middle condition is from 2 to 6 months, which is 2
separate criteria, in order for the formula to work then also you need 2
separate criteria for the other conditions, <2 and 6.

If these numbers represent month numbers then <2 can be interpreted to mean
=0 and <=1. 6 can be interpreted to mean =7 and <=12. This way each
condition has 2 criteria to test for and we can use a single formula and
copy it.


--
Biff
Microsoft Excel MVP


"Ryan L." wrote in message
...
Hi Biff,

Could you please explain why you have the value 0,2,7, 1,6, 12 in column
headers

Thanks & regards,

"T. Valko" wrote:

In order to use a single formula that can be copied you need to create
some
meaningful column headers that can be referenced by the formula.

I assume that month is the month number where 1 = Jan and 12 = Dec.

Let's assume your data is in the range A2:B13 and there are no empty
cells
in B2::B13.

Enter these column headers for the table:

E1:G1 = 0,2,7
E2:G2 = 1,6,12

D3:D6 = A,B,C,D

Enter this formula in E3:

=SUMPRODUCT(--($A$2:$A$13=$D3),--($B$2:$B$13=E$1),--($B$2:$B$13<=E$2))

Copy across to G3 then down to E6:G6.

--
Biff
Microsoft Excel MVP


"Ryan L." <Ryan wrote in message
...
Hi All,

I have the following data:

Product Month
A 1
B 1
C 1
A 2
B 3
A 4
B 7
C 2
D 3
B 1
C 2
D 9

Could you please provide me the formula to count A, B, C, and D with
below
conditions:
<2 months 2 - 6 months 6 months
A count= ? ? ?
B ? ? ?
C ? ? ?
D ? ? ?

Thanks & regards,