seo Question posted in
Some basic information to get you started with this can be found here.

Sorting for Unique values with multiple IF formula in spreadsheet – SEO

I have a sheet of sales entries sorted by date naturally. Each property can have many entries (cancelations are entered as negative purchases). My ordeal is that I need to consolidate duplicates and sum product columns for SIGNED marked rows only.

Secondly, all 0 values (checked by summing all product columns, if 0 = canceled) need to not be displayed from the get go. Essentially The list must be sorted by 2 criteria, then summed accordingly to create an “Active Accounts” table that further data can be extracted from.

Thirdly, this must all happen in real time. When sales are added, tables should update effectively.

In an ideal world creating an auxiliary column that summed product colums if “signed” and returned yes/no if 0 because it forces me to use a view to display only active accounts, as opposed to this happening dynamically at the tables inception. If necessary, I guess I can go that route but hopefully you guys here can help me out in directing me one way or another.

I am going to do my best to illustrate what I have done/tried so far:

Client     Product 1     Product 2     Product 3    Status
AA            50            50            50        Signed
BB            0             0             50        Signed
AA            0            -50            0         Signed
CC            50            0             0         Signed
CC           -50            0             0         Signed
BB            0             0            -50        Pending
AA            0             0             50        Signed
AA            0             0             50        Pending

What my second sheet will look like given the above data.

 Client     Product 1     Product 2     Product 3    Status
AA            50            0             100       Signed
BB            0             0             50        Signed

At this point in time I only know of “=unique(client)” in A1 to dynamically get a unique list dynamically without a ridiculous formula. And then doing a if(sum($b2:$d2)=0,”no”,yes”)) after the status column that would then be sorted out with a view. My only problem is that “Pending” sales, get pulled into my table. I have tried to solve this by sorting it at the product sum column.

=if(isblank(A3),,(sumif(client,A2,product1)))

This works perfectly for me, but it does not sort for “Signed” “Pending”

I have tried :

=if(isblank(Properties),,(IF(And(Properties=$A2,status="Signed"),SUM(SEO))))

but I get the error “An array value cannot be found.”

I’m really stumped as to what steps to take next to tackle this problem.

Tags:

2

Answers


  1. 0

    These sort of reports can be generated with the QUERY function:

    =QUERY(QUERY(A:E,"select A, sum(B), sum(C), sum(D), E where E = 'Signed' group by A, E label sum(B) 'Product 1', sum(C) 'Product 2', sum(D) 'Product 3'",1),"select * where Col2 != 0 or Col3 != 0 or Col4 != 0",1)

    There are two nested QUERYs here; the inner one sums the values for each client that is “signed”, and the outer one excludes any client that doesn’t have a t least one non-zero summed value.

    Login or Signup to reply.
  2. 0

    IN NEW GOOGLE SPREDSHEETS, I had to change comas for semicolons:

    Works for me in this way:

    =QUERY(QUERY(A:E;”select A, sum(B), sum(C), sum(D), E where E = ‘Signed’ group by A, E label sum(B) ‘Product 1’, sum(C) ‘Product 2’,
    sum(D) ‘Product 3′”;1);”select * where Col2 != 0 or Col3 != 0 or Col4
    != 0″;1)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search