Search ranges, identify column and sumif

[vikosh]

Hi,

I am trying to find a solution to the following.

I need to search ranges (3 ranges as per below example), identify last column that has 1's in it and then do sumif according to the names.
Splitting rows can have any random information, and might be even merged, so i need to set ranges.

A	B	C	D	E	F	G
	1	2	3	4	5	6
name 1	1	0	0	0	0	0
name 2	1	1	1	0	0	0
name 3	1	1	1	1	0	0
name 2	1	1	0	0	0	0
name 1	1	1	1	0	0	0
name 2	1	0	0	0	0	0
name 2	1	1	1	1	0	0
name 1	1	1	0	0	0	0
name 1	1	1	1	0	0	0
name 2	1	0	0	0	0	0
name 2	1	1	1	1	0	0
name 1	1	1	0	0	0	0

<tbody>
</tbody>

In this example sumif would return:

Name 1: 0
Name 2: 2
Name 3: 1

Please direct my thoughts in the right way...

Thank you,
Vikosh

 

[Andrew Poulsom]

Please explainn how you arrive a your results:

In this example sumif would return:

Name 1: 0
Name 2: 2
Name 3: 1

 

[vikosh]

Hi Andrew,

After column has been identified, in this case last column with 1's = column E, it is =SUMIF(A3:A16, A2, E3:E16), =SUMIF(A3:A16, A3, E3:E16), =SUMIF(A3:A16, A4, E3:E16).

So basically, i need to find first column with no 1's and SUMIF previous column... or whatever way is more simple.

Thank you,
Vikosh

 

[Andrew Poulsom]

This is the best I can do so far:

=SUMIF(A$3:A$16,A3,INDEX(B$3:G$16,0,MAX((SUM(B$3:B$16)>0)*1,(SUM(C$3:C$16)>0)*2,(SUM(D$3:D$16)>0)*3,(SUM(E$3:E$16)>0)*4,(SUM(F$3:F$16)>0)*5,(SUM(G$3:G$16)>0)*6)))

 

[Andrew Poulsom]

A shorter formula:

=SUMIF(A$3:A$16,A3,INDEX(B$3:G$16,0,MAX((COUNTIF(OFFSET(A$3:A$16,0,COLUMN(A$3:F$3)),1)>0)*COLUMN(A$3:F$3))))

which must be confirmed with Ctrl+Shift+Enter. If entered correctly Excel will surrond the formula with curly braces {}.

 

[vikosh]

thank you Andrew.
Can you explain how does it work please?

 

[Andrew Poulsom]

This:

COUNTIF(OFFSET(A$3:A$16,0,COLUMN(A$3:F$3)),1)>0

returns an array of TRUE/FALSE depending on whether each column in the range B13:G13 contains the number 1. That array is multipled by COLUMN(A$3:F$3) which returns the array {1,2,3,4,5,6}. The MAX function finds the largest resulting number and that is used as the column number for the INDEX function, which returns the range to SUMIF.

You can use Tools|Formula Auditing|Evaluate Formula to watch the steps.

 

[vikosh]

thank you Andrew.

The formula works well, however, somehow it is not working and i just get 0's...

I suspect it is because of many merged cells in my original worksheet.

I have introduced another row at the bottom of the spreadsheet that has sum for each row - how can i use this row to identify column for SUMIF? basically last cell that has something in this row is the cell of column that needs to be SUMIF'ed...

 

[Andrew Poulsom]

To use the totals in B17:G17:

=SUMIF(A$3:A$16,A3,INDEX(B$3:G$16,0,MAX((B$17:G$17>0)*COLUMN(A$3:F$3))))

confirmed with Ctrl+Shift+Enter.

 

[vikosh]

Thanks, but i am still getting 0's. Is there a problem is if cells A3:A6 are empty?

=SUMIF(A$3:A$16,Lists!D2,INDEX(B$3:G$16,0,MAX((B$17:G$17>0)*COLUMN(A$3:F$3))))