Sumif range with one criteria

Microsoft Excel 2003 (full product)
September 14, 2010 at 12:20:10
Specs: Windows 7
Hello there,

Hope you have time to help me out...
I have 3 horizontal ranges (C2:AF2) (AI:BL) and (BO:CR). C2 looks at a cell (A1) which has a date in it. The first of the month (for now Sept 1). The range from C2 on is +1, therefore AF being September 30 and the same for the next 2 ranges. These are formatted to give me the weeknum. 36, 37, 38....

In row 4 there are numbers. I would like to SumIf the first range (C4:AF4) where the week = a particular week in row 2 (which I chose in another cell (say DA = 36). Then I need to do the same in the next 2 ranges. In the end the formula needs to be divide the first sumif range by the sumif range of the second + the sumif of the third. (C4:AF4/AI4:BL4 + BO4:CR4).

Thank you in advance. Always appreciate your knowledge.
Terry


See More: Sumif range with one criteria

Report •

#1
September 14, 2010 at 12:46:00
Let's work backwards through your post:

re: In the end the formula needs to be divide the first sumif range by the sumif range of the second + the sumif of the third. (C4:AF4/AI4:BL4 + BO4:CR4).

Your words don't match your formula or vice versus.

If you mean SUMIF(1) divided by the SUM of SUMIF(2) and SUMIF(3) then you need to add some parenthesis to your "example":

C4:AF4 / (AI4:BL4 + BO4:CR4)

As written, C4:AF4 / AI4:BL4 + BO4:CR4 means:

Divide SUMIF(1) by SUMIF(2) and then add SUMIF(3) to that result.

OK, so I think this is what you are asking for:

=SUMIF(C2:AF2, DA2, C4:AF4) / (SUMIF(AI2:BL2, DA2, AI4:BL4) + SUMIF(BO2:CR2, DA2, BO4:CR4))

Let us know how that works for you.


Report •

#2
September 14, 2010 at 12:52:49
I thought I had my wording correct. Sorry for the mix up, but you figured it out just the same. The formula works just like I require it to. Thank you again. You guys never disappoint.

Always Thankful,
Terry


Report •
Related Solutions


Ask Question