How to calculate the sum of several parameters?

Question

Google Sheets

Hello!

I can't figure out how to calculate the sum in Google Spreadsheets by several parameters from the drop-down list.

For example, there are three columns: "Type", "Color" and "Amount".
"Type" and "Color" are drop-down lists.
In the "Type" list, there are conditional pencils and felt-tip pens. And in the "Color" list, conditional colors are blue and red.


At the moment, using the "SUMIF" function, I can only calculate the sum of one parameter in one of the columns. For example, only the sum of all blue cells, or the sum of all felt- tip pens .

But, I would like to be able to select in the drop-down list what exactly I want to calculate. Namely, three options: the sum of all red pencils and markers, the sum of only red pencils, the sum of only red markers.


I feel that the answer is somewhere on the surface, but I'm not particularly strong in tables.
Thanks in advance for your replies!

Answer 1

In SUMIF, instead of the condition:, "Синий"
you can write: H1
Then the function will look at what is contained in cell H1 and compare with its contents.
True, "Red felt-tip pens" is not the same as "Red". So you have to fence three-story formulas. Instead of H1, you will have to write something like this:
IF(H1="Красные фломастеры";"Красный";"")
And you also have two conditions at once. The easiest way (although it’s different for anyone) is to use the database query language, i.e. QUERY function. Then the final formula will be something like this:

=IF(H1="Красные фломастеры";QUERY(A4:E15;'select sum(E) where A="Фломастеры" and C="Красный"';IF(H1="Красные карандаши";QUERY(A4:E15;'select sum(E) where A="Карандаши" and C="Красный"';SUM(E4:E15)))