SOLVED

Highlighted
New Contributor

# need help figuring out a formula to use to add figures if they are in certain quarter.

hi  I have two questions:

I have a spreadsheet where I log sales, and next to sales number, on next column, I put what quarter it was from, (like, q1, q2, etc.)

1.

I figured out how to make the different quarters be a different color, but not how to make the entire row that color.  I could only make just the cell that said 'q1' for example, a color.

2.

I have sales figures in one column and in next column it will say q1, or q2, etc.

I want to keep a running total of sales for each quarter, in another column.

Can you help me how to write that function?

3 Replies
Highlighted

# Re: need help figuring out a formula to use to add figures if they are in certain quarter.

1) Select the rows you want to color, let's say rows 2 to 200.

I'll assume that the active cell in the selection is in row 2.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cell to format'.

Enter the formula

=\$B1="Q1"

Click Format...

Activate the Fill tab.

Select cyan.

Click OK, then click OK again.

Repeat the above steps, but with the formula =\$B2="Q2" and light green, etc.

Highlighted
Best Response confirmed by Patti1010 (New Contributor)
Solution

# Re: need help figuring out a formula to use to add figures if they are in certain quarter.

2) In C2:

=SUMIF(B\$2:B2,"Q1",A\$2:A2)

In D2:

=SUMIF(B\$2:B2,"Q2",A\$2:A2)

In E2:

=SUMIF(B\$2:B2,"Q3",A\$2:A2)

In F2:

=SUMIF(B\$2:B2,"Q4",A\$2:A2)

Select C2:F2 and then fill down.

Highlighted

# Re: need help figuring out a formula to use to add figures if they are in certain quarter.

@Hans Vogelaar thank you so much!  this worked perfectly!