Using COUNTIFS with checking string and comparing values

Question

I have the following excel sheet:

I am trying to count the number of times when comparing 2 rows a time the first or second is bigger by Value and Value is not 0.

For example:

I am currently using:

=IFS(A2>A3;"Original";A2<A3;"Fake";A2=A3;"Equal")

Which simply adds a text value to column B by checking 2 Values. What I'd like to add is a statement - & Value>0.

Expected output:

  Value   State
2 50      Fake
3 60
4 50      (empty because row 5 is 0)
5 0
6 0       (empty because row 6 is 0)
7 60

In short - how can I count the number of occurrences when condition #1 is met and condition #2 is met, when the second condition is not a text value but a number comparison?

Thank you for your suggestions.

Can you add what the expected output is for your table?

urdearboy
– urdearboy

2020-02-17 15:58:19 +00:00
Commented Feb 17, 2020 at 15:58 — urdearboy
– urdearboy, Commented Feb 17, 2020 at 15:58
Hi @urdearboy, I've added the expected output.

Jonas Palačionis
– Jonas Palačionis

2020-02-18 08:51:27 +00:00
Commented Feb 18, 2020 at 8:51 — Jonas Palačionis
– Jonas Palačionis, Commented Feb 18, 2020 at 8:51

basic · Accepted Answer · 2020-02-18 12:01:22Z

2

Try following formula:

=IFERROR(CHOOSE((A2>A3)*(A3<>0)*1+(A2<A3)*(A2<>0)*2+(A2=A3)*(A2<>0)*3;"Original";"Fake";"Equal");"")

Edit

With condition that the formula works every second row.

=IFERROR(CHOOSE(((A2>A3)*(A3<>0)*1 + (A2<A3)*(A2<>0)*2+(A2=A3)*(A2<>0)*3)*(MOD(ROW();2)=0);"Original";"Fake";"Equal");"")

edited Feb 18, 2020 at 12:01

answered Feb 17, 2020 at 17:40

basic

12k2 gold badges15 silver badges29 bronze badges

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

3 Comments

Jonas Palačionis Over a year ago

Hi @basic, row 2 is good, row 3 is not, because it is comparing row 3 and row 4, whereas I only need to compare 2 by 2 rows, so only row 2,4,6,8... should have the formula.

basic Over a year ago

Well, then you can copy formula every other row or add a condition that the formula return result in every second row. See edit.

Jonas Palačionis Over a year ago

Thank you, I was looking for a command to paste copied cells into every second row.

Collectives™ on Stack Overflow

Using COUNTIFS with checking string and comparing values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related