Totaling criteria from 2 columns

A

alexy

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
 
B

Bob Phillips

=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)
 
M

mangesh_yadav

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

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

Manges
 
A

alexy

Ok, I've found I've now got a third variable, so I'm going to put all o
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, the
numbers in c and d)
2) What is the formula when it's reading from another page?

Thanks once again
 
A

alexy

Is the follwing legitimate?

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

alexy

Ok, it's wrong, in th
=SUMPRODUCT(--(Sheet1!B1:B100="BT2,DB,CN,CN3,BT1"),--( section, I nee
it to be any of the variables, this isn't counting them unless i
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 b
gone :s
 
M

mangesh_yadav

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
 
M

mangesh_yadav

ok. Here it is:

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

Manges
 
Top