Hi all,
I'm trying to calculate sums of certain items from a drop-down list, only if a certain checkbox is checked. Only problem is, there are multiple entries in the same sheet.
I'll start with the base data sheet: these are applications for different items, which I've grouped together. It's obvious to us which are grouped but Excel doesn't know.
There's a drop down list (white cells in column F) for items, and their corresponding quantity in column G. For each institution that requests items, I also check whether or not they're a school (column I).
For total number of schools, the formula was easy using COUNTIF. It was also easy for total number requested (column F, below) using SUMIF. But I'm having trouble writing a formula that's for counting total number of a certain item from the drop down only if the school checkbox (column I, above) is checked. So total 4x8 raised beds should be 5 for schools, not 0.
Just in case here is the formula for total numbers for a 4x8 raised bed (column F):
=SUMIF('Spring 2025'!F:F,'Prices Jan 2024'!A2,'Spring 2025'!G:G)
Where Spring 2025 F is the drop down column (in first photo), Prices Jan 2024 is a separate sheet in the same file that acts as the reference for the drop down and A2 is the 4x8 raised bed item, and Spring 2025 G is quantity (in first photo). Again this one makes sense and works, but I would like to do the same thing for each group only if the school checkbox is checked.
Thank you!!
Hi @chanisorkin
In each of your grouped rows, below the green headers cells in column E don't seem to be used. Just an idea... in cells E reference the School textbox in column I (absolute row#) and copy down to the next cells:
then format cells in column E with a white font (not done above)
Formula in M2 above:
=SUMIFS(G:G, E:E,TRUE, F:F,L2)