I need to calulate the following - but don't know the best/easiest formula to
use.

tst month
1100 oct
1100 nov
1100 dec
1100 oct
1100 nov
1100 dec
1100 oct
1100 feb
1100 mar
2200 mar
2200 mar
2200 jan
2200 jan
2200 feb

the result would be

1100 oct 3
1100 nov 2
1100 dec 2
1100 feb 1
1100 mar 1
2200 mar 2
2200 jan 2
2200 feb 1

You can use =SUMIF, but you will have to swop your columns, so that you have
months on the left, and figures on the right.
--
Hth

Kassie Kasselman
Change xxx to hotmail


"kfr" wrote:

I need to calulate the following - but don't know the best/easiest formula to
use.

tst month
1100 oct
1100 nov
1100 dec
1100 oct
1100 nov
1100 dec
1100 oct
1100 feb
1100 mar
2200 mar
2200 mar
2200 jan
2200 jan
2200 feb

the result would be

1100 oct 3
1100 nov 2
1100 dec 2
1100 feb 1
1100 mar 1
2200 mar 2
2200 jan 2
2200 feb 1

You could create a pivot table and add the first and second columns to
the Row section and then add a count to the Data section.

You could also somehow list the possible values and use a sumproduct
formula:
=SUMPRODUCT((B23=$B$8:$B$21)*(C23=$C$8:$C$21))
This assumes your data is in the range B8:C21 and the possible values
list begins in B23:C23. Place the formula above in cell D23.

You could also use the Data-Filter-Advanced Filter, copy list to
another location, checkbox-unique values only to create the list of
values.

If the list is updated frequently, the pivot table is the best
option.

Hi

First create a table with unique data.

With your data in columns A:B and headings in row 1, select the columns,
goto Data Filter Advanced Filter select "Copy to another location"
Check "Unique records only" Copy to: D1:E1 Ok

Paste this formula into F2 and copy it down as desired:

=SUMPRODUCT(--($A$2:$A$100=D2),--($B$2:$B$100=E2))

Hopes this helps

---
Per

"kfr" skrev i meddelelsen
...
I need to calulate the following - but don't know the best/easiest formula
to
use.

tst month
1100 oct
1100 nov
1100 dec
1100 oct
1100 nov
1100 dec
1100 oct
1100 feb
1100 mar
2200 mar
2200 mar
2200 jan
2200 jan
2200 feb

the result would be

1100 oct 3
1100 nov 2
1100 dec 2
1100 feb 1
1100 mar 1
2200 mar 2
2200 jan 2
2200 feb 1

If you need data in each row, then either append the value to the
month and count it with countif and separate for the results. (ie
create a column with oct1100 then use countif to see how many).

A simpler method is pivot tables.
Cheers,
Reeza

On Mar 24, 12:56*pm, kfr wrote:
I need to calulate the following - but don't know the best/easiest formula to
use.

tst * * month
1100 * *oct
1100 * *nov
1100 * *dec
1100 * *oct
1100 * *nov
1100 * *dec
1100 * *oct
1100 * *feb
1100 * *mar
2200 * *mar
2200 * *mar
2200 * *jan
2200 * *jan
2200 * *feb

the result would be

1100 * *oct * * 3
1100 * *nov * * 2
1100 * *dec * * 2
1100 * *feb * * 1
1100 * *mar * * 1
2200 * *mar * * 2
2200 * *jan * * 2
2200 * *feb * * 1

First, get your months on the left, and your figures on the right.
Use =COUNTIF(Range,Criteria). Range would be the entire range, including
months and figures, and criteria the month you wish to count

Say you have Months in A1, and figures in B1 - A1:B15

Say you want to count in D2:E13

In D2 enter the month, in E2 enter =Countif(A2:B15,D2), and so on down.


"kfr" wrote:

I need to calulate the following - but don't know the best/easiest formula to
use.

tst month
1100 oct
1100 nov
1100 dec
1100 oct
1100 nov
1100 dec
1100 oct
1100 feb
1100 mar
2200 mar
2200 mar
2200 jan
2200 jan
2200 feb

the result would be

1100 oct 3
1100 nov 2
1100 dec 2
1100 feb 1
1100 mar 1
2200 mar 2
2200 jan 2
2200 feb 1