Nested functions

[Derek Gray]

This is doing my head in and I know the answer must be obvious. I need
a formula for counting data where certain criteria are met in two
columns. I guess it will be a nested function but every combination
I've tried (e.g.IF, COUNTA, COUNTIF, etc) doesn't give me the result I
expect.

 

[Bob Phillips]

Derek,

I think you want something like

=SUMPRODUCT((A1:A100="test1")*(B1:B100="test2"))

assuming the values are strings, just forget the quotes if numeric. The two
ranges must encompass the same rows.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Derek Gray]

Derek,

I think you want something like

=SUMPRODUCT((A1:A100="test1")*(B1:B100="test2"))

assuming the values are strings, just forget the quotes if numeric. The two
ranges must encompass the same rows.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob, you're a marvel. That's done it. I never for a moment considered
the SUMPRODUCT function. Off now to study how it works.

Many thanks

Derek

 

[Bob Phillips]

Derek,

Could I suggest that you look at this previous NG posting where Ken Wright
gives a pretty thorough explanation

http://tinyurl.com/v85r

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)