SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1680778%22%20slang%3D%22en-US%22%3Eneed%20help%20figuring%20out%20a%20formula%20to%20use%20to%20add%20figures%20if%20they%20are%20in%20certain%20quarter.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1680778%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%26nbsp%3B%20I%20have%20two%20questions%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BI%20have%20a%20spreadsheet%26nbsp%3Bwhere%20I%20log%20sales%2C%20and%20next%20to%20sales%20number%2C%20on%20next%20column%2C%26nbsp%3BI%20put%20what%20quarter%20it%20was%20from%2C%20(like%2C%20q1%2C%20q2%2C%20etc.)%3C%2FP%3E%3CP%3E1.%3C%2FP%3E%3CP%3EI%20figured%20out%20how%20to%20make%20the%20different%20quarters%20be%20a%20different%20color%2C%20but%20not%20how%20to%20make%20the%20entire%20row%20that%20color.%26nbsp%3B%20I%20could%20only%20make%20just%20the%20cell%20that%20said%20'q1'%20for%20example%2C%20a%20color.%3C%2FP%3E%3CP%3E2.%3C%2FP%3E%3CP%3EI%20have%20sales%20figures%20in%20one%20column%20and%20in%20next%20column%20it%20will%20say%20q1%2C%20or%20q2%2C%20etc.%3C%2FP%3E%3CP%3EI%20want%20to%20keep%20a%20running%20total%20of%20sales%20for%20each%20quarter%2C%20in%20another%20column.%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20help%20me%20how%20to%20write%20that%20function%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1680778%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1680955%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20figuring%20out%20a%20formula%20to%20use%20to%20add%20figures%20if%20they%20are%20in%20certain%20quarter.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1680955%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301117%22%20target%3D%22_blank%22%3E%40Patti1010%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20Select%20the%20rows%20you%20want%20to%20color%2C%20let's%20say%20rows%202%20to%20200.%3C%2FP%3E%0A%3CP%3EI'll%20assume%20that%20the%20active%20cell%20in%20the%20selection%20is%20in%20row%202.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cell%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D%24B1%3D%22Q1%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20cyan.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERepeat%20the%20above%20steps%2C%20but%20with%20the%20formula%20%3D%24B2%3D%22Q2%22%20and%20light%20green%2C%20etc.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

@Patti1010 

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

@Patti1010 

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

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