How to Apply Filters in One Row that Adjusts Price in Another

monica16145

I have a sheet that tells me the price of a product based on whether it's "Completion" is checked or not. I need to be able to filter the "Position" column to show me "Only Angle A" or "Only Angle B" etc. AND, I need the "Price" column to adjust appropriately when these filters are applied. The idea is that if we only want to capture product "Angle A", the price should adjust down with that filter.

The formula in each row under "Price" is: =IF(COUNT(CHILDREN()) = 0, IF(Completion@row = 1, 0, 47.5), SUM(CHILDREN()))

Please let me know if you can help!

-Thanks

Genevieve P.

Hi @monica16145

You can add IF statements to your current formula that look for a value in the Position column and then return different results based on what it finds.

For example, if you wanted "Angle A" to have the price 30, you can say:

IF(HAS(Position@row, "Angle A"), 30

In your instance you need to also check that the checkbox is blank, so you will want to add this statement after that one, like so:

=IF(COUNT(CHILDREN()) = 0, IF(Completion@row = 1, 0, IF(HAS(Position@row, "Angle A"), 30, 47.5), SUM(CHILDREN()))

Does that make sense/work? If not, it would be helpful to know exactly what you want each price to be based on all the criteria, like so:

• If the box is unchecked and the Position is "Angle A", the value is XXX
• If the box is unchecked and the Position is "Angle B", the value is XXX
• If the box is checked, value is 0

Cheers,

Genevieve

monica16145

Hey Genevieve,

Thanks for lending a hand! I think I need to provide more context in order for this to work.

Every row represents an image I want to buy from a photographer (the other columns are just descriptive). Each image will cost $47.50 (represented at the end of the line). The parent rows represent the cost of the group of photos in a set (so I can collapse them and see the higher level prices). If the completion box is checked, then that means the image already exists and I do not want to buy it again (hence the cost reducing to $0). What I need as an option is to filter for just one "Position", "Angle A" or "Angle B", to see what the price would be if I decided to just get one angle instead of both.

Let me know if I need to clarify or provide more context.

Thanks,

-Monica

Genevieve P.

Hi @Monica

What do you mean by "filter"? You wouldn't be able to apply a filter to the sheet as it will search through on a row-by-row basis. Your CHILDREN formula will still calculate for all the children rows, regardless of the Position.

You could potentially create a Report and apply a filter there, but in this instance you would want the SIN Number to be displayed on every child row as well, so you can GROUP by this value.

For the sheet, there are ways to display the total output using a formula to "filter", but this would be constantly displayed in the sheet. You could add this to another column or to the Parent Row (with all three options) if that would help.

For example, instead of just SUM(CHILDREN()) you can use a SUMIF function to Sum the Children but only IF the position is something specific:

SUMIF(CHILDREN(Position@row), HAS(@cell, "Angle A"), CHILDREN())

So if you wanted all three options to display in the Parent row you could do something like this:

=IF(COUNT(CHILDREN()) = 0, IF(Completion@row = 1, 0, 47.5), "Total Cost: " + SUM(CHILDREN()) + CHAR(10) + "Angle A Sum: " + SUMIF(CHILDREN(Position@row), HAS(@cell, "Angle A"), CHILDREN()) + CHAR(10) + "Angle B Sum: " + SUMIF(CHILDREN(Position@row), HAS(@cell, "Angle B"), CHILDREN()))

Or you could split them out into different columns.

Cheers,

Genevieve

monica16145

Hi Genevieve-

This is what I meant by "filter" on the "Position" column:

I like your idea of adding another column for the sum of only "Angle A", but this formula isn't working for me... SUMIF(CHILDREN(Position@row), HAS(@cell, "Angle A"), CHILDREN())

Genevieve P.

Hi @monica16145

Thank you for clarifying! The Filter function that you've pictured in a sheet doesn't have the ability to automatically SUM by the filter criteria, you would need to do this in a Report instead of a Sheet with Grouping and Summary (see this Webinar, here: Redesigned Reports with Grouping and Summary Functions).

In regards to the formula, if you're placing it in a column that does not include the numbers directly below in the same column, you'll need to reference the "Price" column name inside the CHILDREN function at the end, like so:

=SUMIF(CHILDREN(Position@row), HAS(@cell, "Angle A"), CHILDREN(Price@row))

This tells it which children you want to SUM. Blank or () means the current column. Does that make sense?

Cheers,

Genevieve

monica16145

That makes sense, but it's still not working for me... This is what I'm looking at. Can you tell what I am missing?

Genevieve P.

Hi @monica16145

It looks like you're pasting the formula into a Child Row. Try pasting it into the row above, the Green row! You'll also want to make sure your HAS function is using @cell to check for "Angle A" instead of identifying one cell.

Try pasting exactly this into the cell above:

=SUMIF(CHILDREN(Position@row), HAS(@cell, "Angle A"), CHILDREN(Price@row))

monica16145

Hi @Genevieve P.

That worked! Thanks for your help!

Genevieve P.

No problem! I'm glad we got there in the end 🙂