# Subtotalling Similar Rows (VBA?)

#### steveh8204

##### Board Regular
I've made a spreadsheet which compares values from two sheets (the values in Column I and P in the image link below) and then calculates the differences in Row Q, all simple stuff so far.

The problem I've got is some common values that I use to draw the info (Column F - highlighted) have values in multiple rows due to there begin an entry for each due date (Column L) so the Variance (Column Q -highlighted in Red) works them all out seperately which is incorrect.

Is there a way to show the variance in row Q to be the difference between the PO Total in Column I (the sum of cells I31-I33) and the PO total (repeated in cells P31-P33)?

Ideally when the PO information is similar as in this example Cells P31, P32 and Q31, Q32 would be blank, P33 would be 908.700 and Q33 would be 31,500.

Some of the units are different but the code I have that currently works out the variance converts these.

I know its an ask but I've spent hours trying stuff without any real progress. Thanks in advance for any help. #### mrshl9898

##### Well-known Member
So you want 31500 in Q33 only?

Try: =IF(P31<1,"",IF(COUNTIFS(B:B,B31,I:I,">"&0)=COUNTIF(B\$1:B31,B31),SUMIF(B:B,B31,I:I)-P31,"")) in Q31 copied down

#### steveh8204

##### Board Regular
Hi, thanks sorry I've had a mare in work and couldn't log on at home.

That seems to be exactly what I need except for a couple of instances where it hasn't done what it should.

In the image below you can see the value in Q3 shouldn't be there as there is no variance but its the value from P2.

Theres also no variance near the bottom (highlighted) when there should be one (3,000).

Stranges everywhee else t's wored it out right.

#### mrshl9898

##### Well-known Member
So maybe in Q2:

=IF(P2<1,"",IF(COUNTIFS(B:B,B2,I:I,">"&0)=COUNTIF(B\$1:B2,B2),SUMIF(B:B,B2,I:I)-SUMIF(B:B,B2,P:P),""))

That will fix the problem on rows 2 and 3.

I reckon you must have another 3000 for K135202085 somewhere, explaining that 0 (as I am getting -3000 with just the data in that screenshot)

#### steveh8204

##### Board Regular

Sorry its took a while to respond but I've been away the weekend and been off worrk so not been able to test this.

I've just tested it now and it seems to be fine except for one instance which may be because there are three similar rows to subtotal.

In the example above you can see it has no values in row q even though there is a variance (total is 490,200 and the PO quantity is 458,700)

#### steveh8204

##### Board Regular
Sorry its took so long to reply again but it's took me a while to find the time to trouble shoot this one. It appears to work fine but a couple of random rows aren't calculating it the same as the others. I've double checked the formulas are all similar so I'm not sure where the problem lies.

In the following image the amount circled on the far right has taken the value from the one circled above. If I delete the row highlighted yellow the amount on the far right circled disappears (which means its worked it out correct as the 92,750 total equals the 81,500+11,250).

The formula in M23 (the last column) is "=IF(P23<1,"",IF(COUNTIFS(B:B,B23,I:I,">"&0)=COUNTIF(B\$1:B23,B23),SUMIF(B:B,B23,I:I)-SUMIF(B:B,B23,P:P),""))" so I don't understand why it draws the 412,500. Could it be because Colum H is empty? Last edited:

#### mrshl9898

##### Well-known Member
Can't quite follow this one, can you please upload another screenshot, filter column B by the reference number in question, and include the row numbers and column names in the picture?

#### steveh8204

##### Board Regular
Can't quite follow this one, can you please upload another screenshot, filter column B by the reference number in question, and include the row numbers and column names in the picture?
The 2 images attached are the output. The ones in Green are where it has worked correctly and the ones in Red are where it hasn't. The formula in row Q is the following (the *1000 near the end is to convert the units as Column P units are 1.000 for a thousand) copied all the way down:

=IF(P3<1,"",IF(COUNTIFS(B:B,B3,I:I,">"&0)=COUNTIF(B\$1:B3,B3),SUMIF(B:B,B3,I:I)-P3*1000,""))

Last edited:

#### mrshl9898

##### Well-known Member
Thanks for that info.

I see there's a bit of a misunderstanding, try this, it should get rid of most of the issues at least.

If there are still problems please add in column R, with what you want the result to be.

=IF(B3="","",IF(COUNTIF(B:B,B3)=COUNTIF(B\$1:B3,B3),SUMIF(B:B,B3,I:I)-SUMIF(B:B,B3,P:P)*1000,""))

1,082,243
Messages
5,363,972
Members
400,772
Latest member
solbebe

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...