IF, and multiple criteria

  • Thread starter Thread starter John
  • Start date Start date
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?
 
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.
 
=+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 *
 
One way,

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

entered normally. Adjust to suit.

HTH
Anders Silvén
 
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!
==============================================
 
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
 
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
 
Back
Top