Totaling criteria from 2 columns

[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

 

[Bob Phillips]

=sumproduct(--(D2100=8),--(e2:E100=25))

you can't use whole columns with SP.

--

HTH

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

 

[mangesh_yadav]

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

Don't use D, instead use D1100 or any large number

Manges

 

[alexy]

Thanks a lot mate. It's much appreciated

 

[mangesh_yadav]

Thanks for the feedback.

Manges

 

[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

 

[mangesh_yadav]

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

Manges

 

[alexy]

Is the follwing legitimate?

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

 

[mangesh_yadav]

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

Manges

 

[alexy]

It is now. Nice one

 

[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

 

[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!D1100=6),--(Sheet1!E1:E100=25))+SUMPRODUCT(--(Sheet1!B1:B100="DB"),--(Sheet1!D1100=6),--(Sheet1!E1:E100=25))

and so on.

Mangesh

 

[mangesh_yadav]

ok. Here it is:

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

Manges

 

[alexy]

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