Conditional sums in Excel 2

[pawnman]

I'm pretty new to VBA, so here's my question:

I have an Excel worksheet with 8 columns of data. I need to sum every few rows in a single column, based on a different column (It's figuring out the number of bills in deposits for a bank, so I need to sum only the columns that are bills while leaving out the checks, coins, food stamps, etc). The type is listed in a different column, so I was wondering if there was a way to say "Sum these cells when these other cells say $100, $50, etc".

Thanks for the help!

 

[KenWright]

Are you sure you need to do this in VBA?

This can be accomplished quite simply using standard formulas such as SUMIF and SUMPRODUCT, eg:-

=SUMIF(A:A,"Checks",C:C)

=SUMIF(A1:A100,"Checks",C1:C100)

=SUMPRODUCT((A1:A100="Checks")*(C1:C100))

Apologies if you are hardover on a VBA solution, but wasn't sure if you were aware this was simple with standard functionality.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

have a look at the SUMIF / SUMPRODUCT functions - SUMIF where there is only 1 criteria and SUMPRODUCT for more than 1 criteria - there is no need for VBA for this. If it is needed in VBA, you can use SUMIF in code:
mySum = worksheetfunction.sumif(TypeRange,"Type",SumRange)


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[pawnman]

Yeah, I've actually been using the Sumif to generate summaries for each one. The problem is, the deposit data doesn't have a consistent length. I need to find the number of bills in each deposit.

Here's a little sample:

1 >Declared 1 -8,500.50
Glory GFR 2 +$ 100 6 600
Glory GFR 3 +$ 50 48 2,400.00
Glory GFR 4 +$ 20 75 1,500.00
5 +$ 20 100 2,000.00
6 ~Mixed Coin 1 0.5
7 +$ 20 100 2,000.00

What I need is just the numbers next to the +$XXX summed out to the side, then hide all the tows that don't have a sum in them.

 

[pawnman]

If it healps, I'm thinking that I can use a loop to do this, breaking at the blank rows between deposits. Still, I can't figure out the "conditional sum" problem, and what exactly the conditions would be (since each cell starts with a number then +$, but the other cells also start with a number...)

 

[KenWright]

I find your data somewhat difficult to interpret as I'm still struggling to understand exactly what it is you are trying to do. You can conditionally sum a number of columns based on all kinds of differing values within those columns, but I really am not seeing the structure of your data properly and understanding what your criteria is for the summing.

That aside, the very layout that I can grasp would seem to possibly lend itself towards a Pivot table solution anyway.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[pawnman]

I'm trying to sum the number of items. The number +XX is the value of the items, the next number is the number of items, the next number after that is the total value of the items. What I need is to sum the number of items in EACH deposit.

Hope that clears things up. Really, only two columns are relevant here. The column that give the type (number +, or, or number >declared, etc), and the number of items column, which we need to sum up. Thanks for the help!

 

[KenWright]

OK, lets give you an example of what SUMPRODUCT can do:-

=SUMPRODUCT((RngA="abc")*(RngB="def")*(RngC))

will sum all values in RngC, where the corresponding values on those rows in RngA = "abc" and the corresponding values in RngB = "def". These hardcoded values can also be substituted with Cell addresses that contain the criteria, eg with abc in cell J1 and def in cell J2, the previous formula would become:-

=SUMPRODUCT((RngA=J1)*(RngB=J2)*(RngC))

As a further explanation of how SUMPRODUCT works, read on, but the main thing is to get the criteria correct, and the rest is usually easy:-



The formula below exploits the fact that Excel interprets TRUE as 1 and FALSE
as 0. Take the formula below:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A")*(C9:C20))

This sets up an array that gives you something that looks like this
(depending on the variables of course):-

A B C
9 TRUE * FALSE * 3
10 FALSE * FALSE * 4
11 TRUE * TRUE * 2
12 TRUE * TRUE * 1
13 TRUE * FALSE * 4
14 TRUE * TRUE * 3
15 TRUE * TRUE * 2
16 FALSE * TRUE * 8
17 TRUE * TRUE * 6
18 TRUE * TRUE * 8
19 TRUE * TRUE * 7
20 TRUE * TRUE * 6

Which because TRUE=1 and FALSE=0, is interpreted as:-

A B C
9 1 * 0 * 3 = 0
10 0 * 0 * 4 = 0
11 1 * 1 * 2 = 2
12 1 * 1 * 1 = 1
13 1 * 0 * 4 = 0
14 1 * 1 * 3 = 3
15 1 * 1 * 2 = 2
16 0 * 1 * 8 = 0
17 1 * 1 * 6 = 6
18 1 * 1 * 8 = 8
19 1 * 1 * 7 = 7
20 1 * 1 * 6 = 6
-------------
35

and the SUM bit just adds up all the end values of the products



If you exclude the last part of the formula, so that it becomes:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A"))

then what you end up with is a sum of a bunch of TRUE/FALSE values depending on whether or not the criteria has been met on that row, and this is the same as counting the number of records that meet your criteria. Imagine the above tables without Column C, and the last one would look like the following:-

A B
9 1 * 0 = 0
10 0 * 0 = 0
11 1 * 1 = 1
12 1 * 1 = 1
13 1 * 0 = 0
14 1 * 1 = 1
15 1 * 1 = 1
16 0 * 1 = 0
17 1 * 1 = 1
18 1 * 1 = 1
19 1 * 1 = 1
20 1 * 1 = 1
-------------
8

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[pawnman]

Sumproduct would be excellent if I needed to sum up all the values. But I need to sum them individually. Is there a way to use individual sumproducts and stop when there is a blank row? The information isn't uniform, IE, the first set might have 10 rows, the next 30, the next 12.

 

[KenWright]

Without seeing it in situ, I'm really struggling here to visualise how your data is laid out. Is there anything in the data itself that differentiates the different sets of data from each other then?

Are you perhaps able to send me a sample of the data, or even the workbook itself, so that I can see it in Excel format. You would have to put some highlighting in to give me an example of what you would want summed or grouped though.

ken.wright at ntlworld.com

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

OK, for the benefit of anyone that is interested, the data consisted of blocks of data representing money drops.

Aim of the game being to count, for each Drop, the sum total of the 50 / 20 / 10 / 5 dollar bills, which in the example below will be the 2 + 14 + 1 + 2 = 19 Bills

3 +$ 50 2
4 +$ 20 14
5 +$ 10 1
6 +$ 5 2

Downside is the layout of the data because of all the blanks and interspersed rows of meaningless data, ie for each row, if the row doesn't contain " +$" somewhere in it then it is superfluous:-

[tt]

01/12/2003 13:15:46 Bag Number 50497 DEPOSIT Name Acct
… … … … 1 >Declared 1 -1,719.40
… … Glory GFR 3 +$ 50 2 100
… … Glory GFR 4 +$ 20 14 280
… … Glory GFR 5 +$ 10 1 10
… … Glory GFR 6 +$ 5 2 10
… … … … 7 #Checks 1 1,275.00
… … … … 8 #Checks 1 37
… … … … 9 ?Adjustment 1 7.4
… … … … … … …
… … … … * NET Teller 23 0
… … … … … … …
01/12/2003 13:16:46 BAG Number 50497 DEPOSIT Name Acct
… … … … 1 >Declared 1 -362
… … … … 2 #Checks 1 27
… … Glory GFR 3 +$ 20 13 260
… … Glory GFR 4 +$ 10 4 40
… … Glory GFR 5 +$ 5 7 35
… … … … … … …
… … … … * NET Teller 26 0
… … … … … … …
01/12/2003 13:17:30 BAG Number 50497 DEPOSIT Name Acct
… … … … 1 >Declared 1 -107.4
… … … … 2 #Checks 1 7.4
… … Glory GFR 3 +$ 20 2 40
… … Glory GFR 4 +$ 10 1 10
… … Glory GFR 5 +$ 5 3 15
… … Glory GFR 6 +$ 1 30 30
… … … … 7 +$ 5 1 5
… … … … … … …
… … … … * NET Teller 39 0
… … … … … … …
01/12/2003 13:18:20 BAG AA7 50497 DEPOSIT Name Acct
… … … … 1 >Declared 1 -1,821.81
… … … … 2 #Checks 1 311.81
… … Glory GFR 3 +$ 100 4 400
… … Glory GFR 4 +$ 50 6 300
… … Glory GFR 5 +$ 20 33 660
… … Glory GFR 6 +$ 10 5 50
… … … … 7 +$ 100 1 100
… … … … … … …
… … … … * NET Teller 51 0

[/tt]

Solution I have mailed to the OP involved filling in all the blanks in Cols A:B using Edit / Go To special / Blanks, then type =, UP arrow once and CTR+ENTER. Copied and pasted all data in A:B as values. This copied the data above from the first nonblank into each blank cell.

Then used a helper column to determine whether or not the Deposit Column contained the text " +$", and if not to put "Delete me" in the cell in the helper column. Then filtered on that column for "Delete me", used Edit / Go To / Special / Visible - Deleted those rows, put some headers in and givcen that the data was now in database format, created a Pivot table with what was left, breaking on Date / Drop / Bills / Value.

VBA solution could be simply to run down the Deposit column, find the string " +$", then start adding in the data from the value column till you run out of cells with " +$" in and then put the total in a helper column, then continue down starting again.

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[pawnman]

Ken is the man, and I'm hoping this approach flies with the boss. Not sure just how much of the data I need to hold on to, "for posterity".

The reason the sheet is so darned disorganized is that the original data file was a plain ascii text file, which I imported into Excel. It has been a huge pain in the backside for the past couple weeks, until I decided to seek help and find a way to solve this through clever coding or use of Excel's advanced functions.

 

[KenWright]

OK then, simpler approach and loses no data, though not as nice looking, or as flexible as the Pivot Table:-

With your data in A:G

Insert a blank row above all your data, so that row 1 is completely blank

Cell H2, put in the following formula and copy down
=IF(AND(ISERROR(FIND(" +$",E2)),E2<>"",A2=""),"","Show Me")

Cell I3, put in the following formula and copy down
=IF(AND(I2="Delete Me",I1=""),"Delete Me",IF(ISERROR(FIND("NET",E2:E3)),"","Delete Me"))

Select all of Col I and do Edit / Copy, then Edit / Paste Special / Values. Now with all of Col I selected, do Data / Filter / Autofilter, filter on "Delete Me", then do Edit / Go To / Special / Visible Cells Only, then do Edit / Delete / Entire Row

All that have been lost are some superfluous rows with no real data in them that I can see.

Select all of Col F and do Edit / Go To / Special / Blanks, and then hit the AUTOSUM button on the toolbar

Select all of Col F again and do Edit / Replace / Replace SUM( with SUBTOTAL(9, (Make sure you include that comma)

Now just Select all of Col H and filter on "Show Me"

Any of this or something similar could easily be put into a VBA routine

Regards
Ken..................



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

<grrrrr>

Slight amendment there, put any value in cell I1 before doing the filter on "Delete Me", else it will start the filter from I2 and you lose your heading row

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[macropod]

I take it that the data in Ken's 2nd post on 7 May all appear in once column, and you need to obtain a count of the $ notes banked.

You can do this using an array formula like:
=SUM(IF(ISERROR(FIND("$",A1:A42)),0,TRIM(SUBSTITUTE(MID(A1:A42,FIND("$",A1:A42)+9,4),CHAR(160)," "))^1))
for the full range of the sample data given. Change the range to suit your needs for each deposit (I'd suggest using 'INDIRECT' with reference cells into which you can put the start & end row #s).

Cheers

 

[KenWright]

Hi macropod, the problem is that all the bills need to be counted for each individual drop, and not as a total. Given the large amount of drops, with the number of rows per drop being variable, this complicated things.

It was a lot easier for me as I had the benefit of the spreadsheet.

Regards
Ken

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------