IF, and multiple criteria

J

John

In a spreadsheet with data in numerous rows and columns,
I wish to add the amounts in a particular column, when
specific data appears in the same row in two other
columns.

To use examples:

rows E5:E99 can contain (say) A B or C
rows F5:F99 can contain (say) 1 2 or 3
rows Q5:Q99 contain different (currency) amounts

I have tried, as an array:

=IF(AND((E5:E99="B"),(F5:F99="3")),SUM(Q5:Q99),0)

although this is accepted by Excel as a valid formula, it
returns "0", which is not the sum of the amounts in the
rows of column Q that have "B" in column E and "3" in
column F.

Can anyone help, please?
 
G

Guest

I just learned some information about this - did you array-
enter it? I believe it is ctrl-alt-enter. So hit f2, then
array enter.
 
D

Dave R.

=+SUM(((A1:A10={"A","B","C"})*(B1:B10={1,2,3}))*C1:C10)

array entered, change columns and rows to suite.
in array formulas AND is *
 
A

Anders S

One way,

=SUMPRODUCT(--(E1:E21="A"),--(F1:F21=1),Q1:Q21)

entered normally. Adjust to suit.

HTH
Anders Silvén
 
R

RagDyer

It's really better *not* to hard code your parameters into your formula, but
have them read from another cell so that you can change them quite easily.
In this case, enter them in E1 and F1 respectively.

=SUMPRODUCT((E5:E99=E1)*(F5:F99=F1)*Q5:Q99)
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
D

Daniel Bonallack

I couldn't get Dave R.'s solution to work. Could you? I
kept getting 0.
I fooled around with a VBA custom formula which appears to
work. If you want me to send it to you, just let me know.

regards
Daniel
 
D

Dave R.

Hi Daniel

I just tried it again and it seems to work for me, entering as array with
CSE of course (without this it says #VALUE). The OP would have to change it
to say

=+SUM(((A1:A10="B")*(B1:B10=3))*C1:C10)

but adjust the ranges to apply directly to his problem, the {"A","B","C"}
was just there to show him that he could put as many choices as he wanted in
there, or as few.

Dave
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top