Subtotalling Similar Rows (VBA?)

steveh8204

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

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,633
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
Joined
Aug 20, 2018
Messages
121
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
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,633
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
Joined
Aug 20, 2018
Messages
121

ADVERTISEMENT

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)
 

steveh8204

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

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,633

ADVERTISEMENT

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
Joined
Aug 20, 2018
Messages
121
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:

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,633
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,""))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,961
Messages
5,599,061
Members
414,281
Latest member
Engjamal2021

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