CODEWITHSUNDEEP
vbaexcelsum
user3059686
user3059686

Reputation: 433

Excel Dynamically Add To If Row Contains Something

I have an excel sheet that is used for customer data entry, such as cost of parts, how much they paid for everything, and how they paid for it (cash, credit, or check). Here's my first page to get a better look:

Sheet1

So on the first sheet, you enter all data that you need, such as name, car make, address (the date is dynamic with =TODAY()). When you type "y" in the confirm box, the data is calculated on sheet2 to analyze the monetary values. Here's sheet 2 with data so you can see what I'm talking about:

enter image description here

Pressing the "New Customer" button runs a VBA script that creates a new line on sheet1 for another customer entry, creates another corresponding line on sheet2 for the new line on sheet1 to produce the calculated data, and "stores" both sheet1 and sheet2 of the previous customer data on sheet3.

This leads me to my question.. For sheet2 calculations "Cash Total", "Credit Card Total", and "Check Total", I need to filter through and add only (cash + part cost) if they paid in cash, (card + part cost) if they paid with a card, and (check + part cost) if they paid with a check. My problem is that I cannot figure out how to go row by row and check whether they used cash, card, or credit to pay, and then add the part cost of THAT ROW to the corresponding total box. I imagine a statement such as =IF(G2 > 0, G2 + E2) or something along those lines would work, but I cannot figure it out and would like to see you guys present some solutions. Please note, this (part cost + medium of payment) also needs to work for a dynamic range of cells, as the number of customers for the day is unknown, meaning there could be different amounts of rows entered by the "new customer".

Upvotes: 0

Views: 116

Answers (2)

bbishopca
bbishopca

Reputation: 296

From what I understand you could try something like this 

=SUMPRODUCT(--(F2:F7 > 0),D2:D7)+SUM(F2:F7)

The SUMPRODUCT adds up the part cost of only the rows that have a cash payment. Then that total is added to the total of the cash column. You would just need to change the F column to the other payment methods for each type.

There really isn't a good way to make it dynamic. Just set the range large enough that you will never go over. 

F2:F1000

I did not write the equation specific to your sheet. Try this for cash totals.

=SUMPRODUCT(--(G2:G1000 > 0),E2:E1000)+SUM(G2:G1000)

You also didn't mention anything about multiple payment types. I will think about that and get back to you.

Upvotes: 1

KFichter
KFichter

Reputation: 783

As an amendment to bbishopca's statement, you can probably just use the =COUNT(F:F) statement to find out dynamically how many rows exist in the sheet. So right now you'd put

=COUNT(F:F)

In a cell, say A1, which would output 2. Then set the range from bbishopca's answer to 

F2:INDIRECT("F"&A1)

Repeat for each type of payment.

Upvotes: 1

Related Questions