2

Example data below.

I want to be able to sum the values in Col2 for each occurrence of Col 1, depending on the values in 'Other Cols' that are applied in combination with the value in Col1

Col1------Col2-----Other Cols  
A---------40-------other data  
A-------------------other data  
A-------------------other data  
B---------30-------other data  
B-------------------other data  
C-------------------other data  
C-------------------other data  
C---------90-------other data

For example, the values in 'other data' might mean the value where Col1 = B is not to included, so the correct outcome is 130 (40+90)

If possible I want to be able to achieve the above in a single Query.

In the real-life data there are over 2,000 rows of data and roughly 200 different values for Col1 (growing in size on a daily basis!!)

What I've been able to do myself!

1) I've created a Query that outputs a row for each valid occurrence of Col1 according to the selection criteria applied to 'Other Data', i.e.

A  
C

2) Logically what I want to do next, but I can't do it because I don't know how to, is look back into the original data to find out the Col B values for the values for A and C (i.e. 40 and 90)

3) Then after that, I want to be able to sum the values identified (i.e. 40 + 90), so that in one single Query/cell the answer 130 is returned!!!

Being able to achieve step (2) would be very useful???

Doing (2) + (3) would be perfect!!!

(Note, the value for Col2 is unique to each set of values for Col1 )

player0
  • 124,011
  • 12
  • 67
  • 124
roger
  • 79
  • 1
  • 6

1 Answers1

0
=SUM(IFERROR(QUERY(A2:C, 
 "select sum(B) 
  where C = 'yes' 
  group by A 
  label sum(B)''", 0)))

0

=ARRAYFORMULA(SUM(QUERY(UNIQUE({A2:A, 
 IF(C2:C="",, VLOOKUP(ROW(B2:B), IF(
 QUERY(A2:C, "select B order by A desc,B desc", 0)<>"", {ROW(B2:B),
 QUERY(A2:C, "select B order by A desc,B desc", 0)}), 2)), C2:C}), 
 "select sum(Col2) where Col3='yes' group by Col1 label sum(Col2)''", 0)))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Thank you once again. It's will save me such a lot of time. I can't thank you enough! – roger Sep 01 '19 at 18:02
  • Unfortunately I've run into a problem when I've tried to implement the formula. Referring to the example data, what the formula needs to do is sum ColB whenever there is an instance of 'yes' in ColC for a given value in Col A. i.e. C1 & C2 = x, C3 = yes, is a valid situation in which to sum ColB (this wasn't clear from my question, apologies) – roger Sep 02 '19 at 07:27