SUMIF "next" MIN... How do you do without macros?

S

slim6y

Board Regular
Joined
Oct 2, 2012
Messages
78
I'll C&P a small amount of the data below, but this is what I am trying to do.

In column A there is a number between 1.002 and 1.900 which are grouped together. Then in column B there is a 1 or a zero (completely binary).

I want to Sum all the groups' B columns.

So for example, I have 16 lots of 1.010 where a total of 3 is in column B.

Then I want to move straight to 1.020 and get it to sum column B there too. And so on.

I figured I would use something like SUMIF(FILTER(.... But I am unsure how to get it to look for the minimum column A that has not already counted for. I would also like to Print in the column next to my "sumif" the number that is in column A.

Here is a small selection of the data (I have 2547 repetitions).
[TABLE="width: 156"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]1.002[/TD]
[TD="align: right"]0
[/TD]
[/TR]
[TR]
[TD="align: right"]1.005[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.005[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1.005[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.02[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.02[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.02[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1.02[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

So the Outcome I would like in separate columns:
1.002 0
1.005 1
1.010 3
etc
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
It's OK, I worked it out :)
Seeing as column A is completely predictable (ie +0.01) I just added 0.01 to Column C. Then Countif and Sumif. Easy enough :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Macro to look up and post to columns
Replies
8
Views
141
philb99
P
S
  • Question Question
How do I sum without knowing the range?
Replies
1
Views
300
sGraham24
S
M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
238
MARZIOTULLIO
M
J
  • Solved
Subtotals within Sumifs range
Replies
6
Views
976
jaz44
J
P
  • Question Question
How to obtain average values on columns filter
Replies
3
Views
209
StephenCrump
S

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

Share this page

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
Back
Top