CODEWITHSUNDEEP
arraysexcelsumifs
Greg
Greg

Reputation: 1

Excel: sum values of a column based on multiple options' values of multiple columns

The end goal is to have additional rows in a sheet full of data rows (2 columns value, 2 columns distinctive criteria) that will aggregate all small amounts to avoid displaying those in the pivot.

Here is the screenshot (cannot display image because of newbie acount): https://i.sstatic.net/wN5Bv.jpg

I'm looking for a formula that will decide for each row if it is insignificant based on the sum of all the rows bearing the same criteria (location, business line, desk, customerID, and period).

-If it is insignificant it will display "negligible" in the status column, otherwise "valid". rows that do not match the period criteria will be displayed as "valid".
- the specificity is that the PERIOD column may have more than one option, e.g. a range (Jan,Feb,Mar) because i need the pivot to display on command either jan data only, or feb only, or jan,feb at the same time, etc.
-For the 3 other criteria, I've simply created a concatenated column with the values of the 3 columns to match the concatenated cells for each row.

unfortunately the formula returns a #VALUE error or doesnt take the period criteria into account. Hence i think the problem to be at the criteria range, criteria level, which i don't understand because it worked on much simpler ranges...

the concatenate formula helps restrict the SUM(SUMIFS) to two criteria ranges (concatenate and period).

Please see the formula in the screenshot:
{=IF(SUM(SUMIFS(H6:H13;$J$5:$J$12;CONCATENATE(B6;C6;D6;F6);$G$5:$G$12;$K$2:$K$4))<100;"negligible";"valid")}

Would greatly appreciate any help or advice!

Sorry for the bad formatting.

Greg

EDIT1: screenshot and end goal sought: to have the miscellaneous rows sum up negligible amounts depending on the period range selected.

Upvotes: 0

Views: 590

Answers (2)

Greg
Greg

Reputation: 1

sum(sumifs) with arrays did the job and i just had to match sum range and criteria ranges... thanks to those who took the time to read me.

Upvotes: 0

Nicolaesse
Nicolaesse

Reputation: 2714

If you want to sum each case of the table you'll better make a pivot table with concat in rows and sum(revenues), sum(employees) in the data area.

If you want to sum only certain cases consider using the SUMIFS function instead of the concatenate.

Upvotes: 0

Related Questions