Hi and thanks in advance,
How do I make a formula (I'm guessing sumif may work??) that will total items if a condition in another column is met? For example, I'd like to total the number of Long and Short strategies but ONLY if they're Open like the yellow boxes in the screeshot. If they're closed, I don't want to total them.
The formula provided by badunit works.
=COUNTIFS(B2:B7,"open",A2:A7,"long")
Now as a learning experience maybe you want to know why yours doesn't.
1- the calculation includes the result cell B9 itself, as it is within the blue rectangle. It cannot be, you only want to include cells B2:B7.
2- the use of AND is unnecessary because you only have one argument inside it, and harmful because it returns a single true/false value that's incompatible with COUNTIFS. COUNTIFS itself does an AND between all pairs of arguments, so the logic is: count the number of rows where on the same row "Open" is the value in the first range (B2:B7) AND "Long" is the value in the second range (A2:A7).
To make the formula easier to use for both the Long total and the Short total I'd modify it this way so it can be used for both totals without manual modification. Enter first in B9 and then copy and paste in B10 (or copy-drag with the yellow bubble at the base of the cell).
=COUNTIFS(B$2:B$7,"open",A$2:A$7,$A9)
The formula provided by badunit works.
=COUNTIFS(B2:B7,"open",A2:A7,"long")
Now as a learning experience maybe you want to know why yours doesn't.
1- the calculation includes the result cell B9 itself, as it is within the blue rectangle. It cannot be, you only want to include cells B2:B7.
2- the use of AND is unnecessary because you only have one argument inside it, and harmful because it returns a single true/false value that's incompatible with COUNTIFS. COUNTIFS itself does an AND between all pairs of arguments, so the logic is: count the number of rows where on the same row "Open" is the value in the first range (B2:B7) AND "Long" is the value in the second range (A2:A7).
To make the formula easier to use for both the Long total and the Short total I'd modify it this way so it can be used for both totals without manual modification. Enter first in B9 and then copy and paste in B10 (or copy-drag with the yellow bubble at the base of the cell).
=COUNTIFS(B$2:B$7,"open",A$2:A$7,$A9)
=COUNTIFS(B2:B7,"open",A2:A7,"long")
Take a look at the COUNTIFS function.
ya I was messing around with that but couldn't get it to work. I'm guessing it's the amperstand operator? What would I use for the &?
This doesn't work either :(
SumIf question - total items in a column if it meets a criteria
