Hey everyone, I’m stuck on a Sheets problem. I need to count ‘Yes’ answers in two columns, but only if they fall within a certain time period. For example, I want to tally the ‘Yes’ responses in the adjacent columns when X is in Q3.
I tried this formula, but it’s not working:
=IF(TimeColumn = "Q3", COUNTIF(Column1, "Yes") + COUNTIF(Column2, "Yes"), 0)
Am I on the right track? What am I missing here? Any help would be awesome! Thanks in advance!
I’ve encountered a similar issue before. The SUMPRODUCT function is indeed a good approach, but you might want to consider using COUNTIFS for better readability and flexibility. Here’s an alternative formula you could try:
=COUNTIFS(TimeColumn, “Q3”, Column1, “Yes”) + COUNTIFS(TimeColumn, “Q3”, Column2, “Yes”)
This formula counts ‘Yes’ responses in both columns separately, but only when the corresponding time is Q3. It’s more explicit and easier to modify if you need to add more conditions later. Remember to adjust the column references to match your actual sheet layout.
hey surfingwave, ur formula’s close but not quite there. try using SUMPRODUCT instead. It’ll let u combine multiple conditions. something like:
=SUMPRODUCT((TimeColumn=“Q3”)*(Column1=“Yes”)+(Column2=“Yes”))
this should do the trick. lmk if u need more help!
I’ve dealt with similar counting challenges in Sheets before. While SUMPRODUCT and COUNTIFS are solid options, I’ve found array formulas to be incredibly powerful for these scenarios. Here’s an approach that might work well:
=SUM(ARRAYFORMULA(IF(TimeColumn=“Q3”, (Column1=“Yes”) + (Column2=“Yes”), 0)))
This formula creates an array of 1s and 0s for each row where the time is Q3 and either column has a ‘Yes’, then sums it all up. It’s flexible and can handle large datasets efficiently.
Just make sure to replace TimeColumn, Column1, and Column2 with your actual column references. If you’re working with a lot of data, this method can be faster than multiple COUNTIFS.