Hi, I have a sheet that has columns D and E, in column D I have size (8,
10 12, etc.) and in E I have Trouser length (29, 31).
They are both discrete numeric values.
How can I get a count of all the rows when d:d=8 and e:e=25 ?

Thanks a lot.


--
alexy
------------------------------------------------------------------------
alexy's Profile: http://www.excelforum.com/member.php...o&userid=25905
View this thread: http://www.excelforum.com/showthread...hreadid=392835

=sumproduct(--(D2:D100=8),--(e2:E100=25))

you can't use whole columns with SP.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"alexy" wrote in
message ...

Hi, I have a sheet that has columns D and E, in column D I have size (8,
10 12, etc.) and in E I have Trouser length (29, 31).
They are both discrete numeric values.
How can I get a count of all the rows when d:d=8 and e:e=25 ?

Thanks a lot.


--
alexy
------------------------------------------------------------------------
alexy's Profile:
http://www.excelforum.com/member.php...o&userid=25905
View this thread: http://www.excelforum.com/showthread...hreadid=392835

use:
=SUMPRODUCT(--(D1:D100=8),--(E1:E100=25))

Don't use D:D, instead use D1:D100 or any large number

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=392835

Thanks a lot mate. It's much appreciated.


--
alexy
------------------------------------------------------------------------
alexy's Profile: http://www.excelforum.com/member.php...o&userid=25905
View this thread: http://www.excelforum.com/showthread...hreadid=392835

Thanks for the feedback.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=392835

Ok, I've found I've now got a third variable, so I'm going to put all of
my totals onto sheet 2, and have my raw data on sheet 1.
Now I have 2 questions,
1) Can I have a variable that is text (Ie, BT2 in column B, then
numbers in c and d)
2) What is the formula when it's reading from another page?

Thanks once again.


--
alexy
------------------------------------------------------------------------
alexy's Profile: http://www.excelforum.com/member.php...o&userid=25905
View this thread: http://www.excelforum.com/showthread...hreadid=392835

=SUMPRODUCT(--(Sheet1!D1:D100="BT2"),--(Sheet1!E1:E100=25))

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=392835

Is the follwing legitimate?

=SUMPRODUCT(--(Sheet1!B1:B100="BT2,DB,CN,CN3,BT1"),--(Sheet1!D1:D100=6),--(Sheet1!E1:E100=25))


--
alexy
------------------------------------------------------------------------
alexy's Profile: http://www.excelforum.com/member.php...o&userid=25905
View this thread: http://www.excelforum.com/showthread...hreadid=392835

Yes. Very much.
Aren't you getting the right answer...?

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=392835

It is now. Nice one.


--
alexy
------------------------------------------------------------------------
alexy's Profile: http://www.excelforum.com/member.php...o&userid=25905
View this thread: http://www.excelforum.com/showthread...hreadid=392835

Ok, it's wrong, in the
=SUMPRODUCT(--(Sheet1!B1:B100="BT2,DB,CN,CN3,BT1"),--( section, I need
it to be any of the variables, this isn't counting them unless it
satisfies all of them. I want it to be if it's DB or CN or... etc.
How can I acheive this?

God, I didn't realise after 2 years of no excel use it would all be
gone :s.


--
alexy
------------------------------------------------------------------------
alexy's Profile: http://www.excelforum.com/member.php...o&userid=25905
View this thread: http://www.excelforum.com/showthread...hreadid=392835

I thought you are searching for the whole string: "BT2,DB,CN,CN3,BT1".
But...


I believe there exists an easier way out, but for the moment here's an
alternative:

=SUMPRODUCT(--(Sheet1!B1:B100="BT2"),--(Sheet1!D1:D100=6),--(Sheet1!E1:E100=25))+SUMPRODUCT(--(Sheet1!B1:B100="DB"),--(Sheet1!D1:D100=6),--(Sheet1!E1:E100=25))

and so on.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=392835

ok. Here it is:

=SUM(--(Sheet1!B1:B100=TRANSPOSE({"BT2";"DB";"CN";"CN3";" BT1"}))*(Sheet1!D1:D100=6)*(Sheet1!E1:E100=25))

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=392835

I swapped the SUM for SUMPRODUCT and it worked fine. Thanks.


--
alexy
------------------------------------------------------------------------
alexy's Profile: http://www.excelforum.com/member.php...o&userid=25905
View this thread: http://www.excelforum.com/showthread...hreadid=392835