Count based on drop-down selection criteria

Question

I have a sample dataset similar to the screenshot below in column C:E.

I am trying to have a formula that will return the total count (B9) based on the dropdown selection in A2.

If 'All Items' is selected, the formula will count all records that have value (C) that is >50. Count 13

If 'Blue' is selected, the formula will count all records that are 'Blue' in Type (D). Count 7

I have a helper column (E) to return TRUE if the Amount (C) is >50.

=ARRAYFORMULA(IF(C2:C>50,1,0))

You shouldn't rely on screenshots. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. — doubleunary, Commented May 9 at 17:31

doubleunary · Accepted Answer · 2024-05-09 17:27:13Z

1

Use filter(), like this:

=counta(iferror(filter(D2:D, (A2 = D2:D) + (C2:C > 50) * (A2 = "All items (>50)"))))

answered May 9 at 17:27

doubleunary

15.7k9 gold badges23 silver badges59 bronze badges

Add a comment |

Blindspots · Accepted Answer · 2024-05-10 22:43:26Z

0

@doubleunary's answer is correct this is simply an alternative function SUMPRODUCT

=SUMPRODUCT(D2:D<>"",((A2=D2:D)+(A2="All items (>50)")*E2:E))

If you are only using the helper column E for this formula you can remove it and use the following

=SUMPRODUCT(D2:D<>"",((A2=D2:D)+(A2="All items (>50)")*(C2:C>50)))

answered May 10 at 22:43

Blindspots♦

7,4825 gold badges20 silver badges38 bronze badges

Add a comment |

2 Answers 2