Search ranges, identify column and sumif

vikosh

Board Regular
Joined
Dec 12, 2011
Messages
87
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

vikosh

Board Regular
Joined
Dec 12, 2011
Messages
87
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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 {}.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

Board Regular
Joined
Dec 12, 2011
Messages
87
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Dec 12, 2011
Messages
87
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))))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,952
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top