Counting in two columns
 

A spreadsheet contains 3 columns: "Item", "Week no." and "Year".

F.ex.:

A B C
1 Item Week Year
2 101 32 2006
3 101 32 2006
4 101 32 2006
5 101 32 2007
6 101 32 2007
7 102 32 2006
8 102 33 2006

In another cell I wish to count how many of the different Items (f.ex. 101)
are listed in f.ex. week 32 AND year 2006, but only for these. In the above
case the result should be 3.

It seems simple, but I just can not figure it out. Can anybody give me the
formular for this? Thanks.

Counting in two columns
 

=SUMPRODUCT((A2:A8=101)*(B2:B8=32)*(C2:C8=2006))

Regards

Trevor


"PedersenJ" wrote in message
...
A spreadsheet contains 3 columns: "Item", "Week no." and "Year".

F.ex.:

A B C
1 Item Week Year
2 101 32 2006
3 101 32 2006
4 101 32 2006
5 101 32 2007
6 101 32 2007
7 102 32 2006
8 102 33 2006

In another cell I wish to count how many of the different Items (f.ex.
101)
are listed in f.ex. week 32 AND year 2006, but only for these. In the
above
case the result should be 3.

It seems simple, but I just can not figure it out. Can anybody give me the
formular for this? Thanks.

Counting in two columns
 

Trevor, you´re a star. That worked perfect for half my needs.

If instead of "101" in the example below the column uses words (f.ex.
"Buttom", Middle" and "Top", what should the formular then be? (it seems that
it your suggestion works with numbers.......).

Thanks and best regards



"Trevor Shuttleworth" skrev:

=SUMPRODUCT((A2:A8=101)*(B2:B8=32)*(C2:C8=2006))

Regards

Trevor


"PedersenJ" wrote in message
...
A spreadsheet contains 3 columns: "Item", "Week no." and "Year".

F.ex.:

A B C
1 Item Week Year
2 101 32 2006
3 101 32 2006
4 101 32 2006
5 101 32 2007
6 101 32 2007
7 102 32 2006
8 102 33 2006

In another cell I wish to count how many of the different Items (f.ex.
101)
are listed in f.ex. week 32 AND year 2006, but only for these. In the
above
case the result should be 3.

It seems simple, but I just can not figure it out. Can anybody give me the
formular for this? Thanks.

Counting in two columns
 

If it's text you can use the same formula but you need quotes around the
text, e.g.

=SUMPRODUCT((A2:A8="Top")*(B2:B8=32)*(C2:C8=2006))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=569220