Thread: Summing in Spreadsheet with Conditions

Hello.

I've created a spreadsheet meant to keep track of particular types of expenditures based on a special coding applied to vendors to whom payments have been made using one of four types of funding. I've attached the sheet to this message.

In order to sum up the expenditures based on vendor status ("Y" or "N") and time period (1st, 2nd, 3rd or 4th), I'm left to use a formula such as this:
=SUMIFS(L15:L10000,E15:E10000,"Y",J15:J10000,"1st")

What I would like this formula to do is adjust the number of active data rows. Although my data entry will begin in row 15, it won't necessarily end in row 10000. How should I write the formula so it adjusts for situations where, for example, rows 15 through 93 are the only ones used? (please note that this calculation cell is located at N10).

Also, if you look at the cells highlighted in green, I would like to have the cells in row 10 be able to total based on the conditions indicated in their respective columns. For instance, Column O (the LBE Total column) is now reliant on summing data found in the column beginning at row 15. Ideally, I don't want the total found in O10 to be dependent on the data in the column (mainly because if all the cells in the row aren't formatted to produce a dollar value, some expenditures could be missed). I want cell O10 and the others similar to it to combine the function occurring in cells like O15, which searches data in column D in order to determine whether or not to display data in column L, with a summing function to capture all of the criteria-based dollar amounts.

I hope this wasn't too confusing but I ultimately want to make this form "smarter" than it has been in some time.

Thanks for any help you can provide.

Dear Excel expert .
Pls guide me VBA code , so that i can acheive following

Pls find the spreadsheet attached
A)I want to sum up column R , AA, AC, AE,AG, AI, AK, AM , AO.
B) I want other column to default detail as per first record.

condition - Create a new record in last. If both Column BF , Column Q combination is duplicated in file. ( example in attachment row no 2 & 7 are duplicate).

for example
I mean create a new record in Row 9 by summing up row 2 & 7 for column (R , AA, AC, AE,AG, AI, AK, AM , AO) & default other data from row 2 in row 9 ( for columns other than R , AA, AC, AE,AG, AI, AK, AM , AO ). Highlight new record in green colour. Refer sample new record in column 11.

Highlight duplicate column in red colour & enter text in column BM ( "To be deleted") for row 2 & 7.