Formula Help

Occasional Contributor

Hello, attached is an example of the file I am needing a formula for.

 

I am looking for a formula that will add up the points for a week per person. I have to do this for each week for the next 4 months. I was trying a Xlookup combo since the names can change rows when they are filtered.

2 Replies
Hello @CatherineMadden Thanks for the attached workbook, I was unable to figure out what you really need. I know that these kinda situations come up every so often, you use this workbook a lot and we do not, so we need explicit description of the situation. From what I read it seems that you want to add the values in certain cells based on some certain conditions.

For this we can use the SUMIF() function.
Let me describe how it will work. In the workbook that you sent do the following:
A) On the "Weekly Data" worksheet in cell H4 place the value 65.
B) On the "September" worksheet in cell BU5 place the value 77
C) On the "September" worksheet unhide Column A.
We are now set:
Lets write the formula:
D) On the "Weekly Data" worksheet in cell H9 paste this formula:

=SUMIF($B$4:$B$7,B4,H4:H7)+SUMIF(September!$A$5:$A$8,September!A5,September!$BU$5:$BU$8)

You will see the value 142 (65+77).

What happened is that MS-Excel looked for "Abel Shane" and found the row its on in the range $B$4:$B$7 on the "Weekly Data" worksheet. That was Row 4.
Then MS-Excel went to the range H4:H7 and grabbed the value 65.
Same thing happened on the "September" worksheet, we looked the value "Abel Shane" and found its row (Row 5 this time), then it went to the range $BU$5:$BU$8 and retrieved the value in BU5 77 and added it up to the 65 value giving the 142.

I wish I could color code all these parts so that it will be better tracked, but I am new at this thing.

I hope I gave you a starting point so that you can continue on your own. But we are all behind you so if you need any help, just come back and let us know, but please be specific, give great examples. If you need excellent help.

Georgie Anne

@CatherineMadden 

The way the September sheet is arranged makes it very difficult to analyze the data. Impossible? No, it can be done with some of the functions available to Insiders and those using Excel 365 (Current channel).

 

The easiest data arrangement to analyze is when data runs vertically with as few columns as possible.  Merged cells should be avoided if at all possible.  I've re-arranged the September sheet and created the SUMIFS and AVERAGEFIS.

Revamped September sheet:

Patrick2788_1-1664059530643.png

 

Additionally, I found and removed 3500+ tiny pictures from this workbook:

Patrick2788_0-1664059439110.png

I found them by going to Home | Find and Select | Selection Pane.  I noticed the workbook was a bit sluggish to navigate.  An abundance of objects makes Excel work harder to render them on screen even if they're tiny and practically invisible.