Is there a function in VB or Excel that compares a column range with a certain value and returns a sum?

Question

Hi there!!

The fact is this, I have two columns of data, first column are dates and second column are numbers. It happens that column 1 can have the same date repeated (for example in 18 rows we have just 4 dates), so I want to be able to compare a certain date with column 1 and then when a cell in column 1 is equal to the date I want, I want to sum the numbers of the cells corresponding to the same date. (Did I make myself clear??)

Is something like a summary by date, but comparing through specific dates.

Thanks a lot!!

Answer 1

{=SUM(IF($A$2:$A$11 = DATEVALUE("11/29/06"), $B$2:$B$11, 0))}

This example (an array formula, so hit CTRL+Shift+Enter when you finish typing it instead of just Enter (don't type the brackets - { } )) sums the contents of Column B for every instance the Column A contains 11-29). Just modify it to match your cell references and it should work just fine.

Answer 2

Try using a pivot table. Excel has a wizard that steps you through the process. You'll put the date column for the left column of the table and the column of numbers for the heading of the pivot table. Then drag the number column to the body of the table. The wizard defaults to a sum on that value. Hope that helps.

Answer 3

in case you utilize the SUM function, you will constantly could define the selection. in case you utilize SUBTOTAL as a substitute "=SUBTOTAL(9,B2:BXX)", you will basically sum up what you tutor on the demonstrate screen. in case you do an autofilter by column A to tutor basically the conventional values in A whose column B documents you desire to SUBTOTAL, the only values in B which will upload up would be those belonging to the comparable fee in Column A. no could type. besides the undeniable fact that, you ought to use the Subtotals function. First pick column A&B on the perfect, circulate to documents --> type, pick Ascending by Column A. Then circulate to documents --> Subtotals, then pick "At each and every substitute in (Column A's call)", "Use function Sum", and "upload subtotal to (Column B's call). you will finally end up with Pumpkin finished seventy 8 Orange finished ninety 4