Subtotalling Similar Rows (VBA?)

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
143
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.

view
https://drive.google.com/open?id=1psqKBmGuZLtTYBHFM9ZDHi_6eI2m2yz7
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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.


https://drive.google.com/file/d/1matsScNoD0Mf4I1RiUlNK_rmohw8t_1K/view?usp=sharing
 
Upvote 0
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)
 
Upvote 0
https://drive.google.com/open?id=1-ZlVi6HxvqbmJ6ujxAADrK5uWfOX553c

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)
 
Upvote 0
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?

view


https://drive.google.com/file/d/1pCnuJ5nbaedriM2hHJEGHNLEEavRnlzq/view?usp=sharing
 
Last edited:
Upvote 0
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?
 
Upvote 0
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,""))

https://drive.google.com/file/d/188Y3bwsTww02ofJsaEHXGBs4wZnMMlIi/view?usp=sharing


https://drive.google.com/file/d/1talqrTL2mkO2a_mdIv8ciHGw2E2PPUaF/view?usp=sharing
 
Last edited:
Upvote 0
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,""))
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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