Complicated SumIfs/Sumif functions

**WadeLair** · 01-24-2013, 07:41 PM

I need some help with a formula.

In my attachment, I'm really referring to the cells that appear at f146 - that would be the first one.

So, I want to sum up cells that meet a few criteria - for the "Current," line, the values to sum would come from other "Current" rows.

For the "Weekday," portion, the values to add would come from the values listed as weekday in column a.

The difficult part is - the length of the month changes all year and I will never know which month will be thrown at me.

To verbalize that formula, I need to sum up values from column F that are in the same month we are trying to total, are the current figures and are weekdays. I need the formula to be flexible enough and actually search for the appropriate values to add from the range F22:F700 (so I can use the same formula regardless of the changes the calendar causes).

Any help would be GRREATLY appreciated.upload.xlsm

**bebo021999** · 01-24-2013, 08:13 PM

Try this:
=SUMIF($A$19:$A145,"Weekday",$F$19:$F145)-SUMIF($D$19:$D145,D146,$F$19:$F145)

**WadeLair** · 01-25-2013, 10:46 AM

Thank you! Can you explain the second SUMIF? I don't understand what it is evaluating.

**bebo021999** · 01-25-2013, 10:59 AM

First SUMIF will sum all "Weekday" so far, second SUMIF sum all "Weekday Total" so far
For instance, if current month is March, secon SUMIF sum all "Weekday Total" in upper rows (there are two of Jan and Feb) while the first SUMIF sum all weekdays.
Pay atention that I use relative address in $A$19:$A145.

**WadeLair** · 01-25-2013, 01:19 PM

This is correct only in the first month, in the months beyond that, it doesn't work correctly.

Could I add if statements to evaluate if the numbers are in the correct month? Maybe I would have to add indirects somewhere in the sheet to be sure this is summing the correct numbers. Any more ideas?

It seems like the range should end around aruond row 700.