CODEWITHSUNDEEP
excelexcel-formula
Joseph Henriquez
Joseph Henriquez

Reputation: 23

Excel: Adding numeric value to yes/no

I am making a spreadsheet for some calendar orders that I have organised.

Currently I have a column which has the unit price of $14.50, then I have a column for quantity (number of orders placed), then a subtotal (unit price * Quantity) and finally a paid column, which is a simple dropdown box of yes || no.

The formulas that I have created thus far are working. But now that I have gone to print I want to keep track of payments. So, what I would like is when someone has paid to subtract their subtotal (unit price * quantity) from the grand total. So, in the paid column of yes || no, when yes is selected it subtracts the subtotal from the grand total.

I was wondering if someone could find a solution for this. A far as I can figure out the formula needs to consist of adding a value to yes and no, but also somehow adding and factoring in the subtotal value. I am quite unsure of this, so this is why I am seeking help.

*note. The yes and no selection is a range that spans across 2 columns. the first range is from H5:H62. the second range is from Q5:Q62

Upvotes: 0

Views: 1708

Answers (2)

Dane I
Dane I

Reputation: 742

=SUMIFS(Subtotal_Range, Paid_Column, "<>Yes")

Sums your subtotals if the Paid column does not equal YES

Upvotes: 1

outlyer
outlyer

Reputation: 3943

One possible way is to modify the grand total calculation, to only SUM the unpaid subtotals, with a conditional sum (SUMIF or SUMIFS). e.g.:

SUMIFS(C5:C62; H5:H62; "<>Yes"; Q5:Q62; "<>Yes")

Where C is the subtotals column. I'm not sure if you mean that both H and Q have the same Yes/No values, but this should serve as a start.

Upvotes: 1

Related Questions