excel nested if multiple logical tests

Question

I have read other threads on this topic, however I found no answers. I have three colums, of which I want to test column a and b and if true I want the som of the results in colum c.

         a      b      c
  1     ba     za       2
  2     ba     az       2
  3     ca     za       2
  4     ca     az       2 
  5     ba     za       2

I want to find the sum if column a = ba and column b = za. So in this example the sum of those would be 4. This is the Excel forumala I have, however it keeps giving the result of 0.

This formula is a nested if.

=Sum(IF(And((a1:a5="ba");b1:b5="za");c1:c5;0))

Can someone please help me with finding the correct formula? I have been breaking my head about this particular formula.

I have been using excel for a while now, and quite got the hang of all the formulas, however this is the only problem I could not wrap my head around.

What Excel version? Maybe simply SUMIFS is the solution? support.office.com/en-us/article/… — Axel Richter
– Axel Richter, Commented May 28, 2015 at 9:58
Did you find any answer useful? (you did not provide feedback/marked answers). — sancho.s ReinstateMonicaCellio
– sancho.s ReinstateMonicaCellio, Commented Feb 15, 2019 at 21:47

Tom · Accepted Answer · 2015-05-28 09:59:16Z

1

You should be using commas instead of semi-colons. I'd suggest adding another column (Lets say column C) and input the formula:

=IF(AND(A1="ba", B1="za"), 1, 0)

You can hide this column from view if you want. Then use this to get your answer

=SUMIF(C1:C5,1,D1:D5)

so my dataset looks like:

+---+----+----+---+----+
|   | A  | B  | C | D  |
+---+----+----+---+----+
| 1 | ba | za | 1 |  2 |
| 2 | ba | az | 0 |  2 |
| 3 | ca | za | 0 |  2 |
| 4 | ca | az | 0 |  2 |
| 5 | ba | za | 1 |  2 |
+---+----+----+---+----+

Which gives me an answer of 4

Or you could use SUMIFS

=SUMIFS(D1:D5,A1:A5,"ba",B1:B5,"za")

Which will also give you an answer of 4

edited May 28, 2015 at 9:59

answered May 28, 2015 at 9:40

Tom

9,9184 gold badges34 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:14:25Z

1

As per Tolerant average (ignore #NA, etc.) and its answers, this should work.

=SUM(IF(($a$1:$a$5="ba")*($b$1:$b$5="za"),$c$1:$c$5))

entered as an array formula using CtrlShiftEnter↵.

=SUMPRODUCT(($a$1:$a$5="ba")*($b$1:$b$5="za")*($c$1:$c$5))

should work as well.

Note that you may have to change usage of absolute/relative referencing.

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered May 28, 2015 at 11:02

sancho.s ReinstateMonicaCellio

15.8k25 gold badges110 silver badges214 bronze badges

Collectives™ on Stack Overflow

excel nested if multiple logical tests

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related