SUMIF Year-To-Date and Horizontal Summing

Hello all,

I have a table that is build as follows (attached file)excel question-sumif.xlsx:

col A: names of salespeople

row 2: months of the year. col 2= Jan-2012, Col 3= Feb-2012 and so on, until Dec-2012.

inside the table: sales numbers (for Oct-Dec, these are projected sales).

What I'm trying to do is to sum the year-to-date sales of each salesperson.

For that I did the following:

chose an empty cell and wrote: =text(today(),"mmm-yyyy") to match the formatting of the months row.

Then I tried to follow what's written in this thread:
http://www.excelforum.com/excel-gene...-equal-to.html


with the necessary adjustments to make it horizontal instead of vertical.

However, all I got was 0s.

Could you please help?

Thanks a lot!

Hi ANS, wellcome to the forum.
Nicely conceived post.

In your cells B3 to J3 you have text.
You should rather put dates (for example instead of APR-2012 write 1.4.2012 and format it as mmm-yyyy).
Then your formula will work.

Also change ranges to be same: B$3:M$3 instead of A$3:M$3

Haha! It works! Beautiful!

Thank you Ace_XL and zbor, and thank you for welcoming me to your forum :D

Guys,

a follow-up question, now that the months are set correctly.

I'd like to add a row which, for each month, indicates what % of the month has passed so far. (mtd)
So, the desired outcome should be:
for months Jan-Aug = 1
Sep = % (which happens to be 1 too, today)
Oct-Dec = ""

This is the formula I used (also in attached file):

=IF(B5<=$S$6,IF(B5<>$S$6,1,(DAY(TODAY())/DAY(EOMONTH(TODAY(),0)))),"")

excel question-sumif-2.xlsx

5 is the row of months, B5=Jan 2012, C5 = Feb 2012, and so on.

$S$6 is the cell that states the current month, =TEXT(TODAY(),"mmm-yyyy")


and I get 1s, even for future values.
I'm not sure if for september it actually shows the right %, or is it just a coincidence, since today is the last day of the month. (i tried subtracting 1 from todays date, but something broke)

Can anyone spot the error?

Thanks in advance

Hi Ace,

Thanks for the reply.

It still doesn't work. I get weird values. 0.0645 for September (which should get 1, since we're in October already) and 1 for Oct-Dec. It should of course be about 6-7% for October, and nil for Nov and Dec.

Any idea why that is?

Thanks a lot!

Originally Posted by Ace_XL

Since S6 gives you text values, use P6

Hence,
=IF(B5<=$P$6,IF(MONTH(B5)<>MONTH($P$6),1,IF(MONTH(B5)=MONTH($P$6),DAY(TODAY())/DAY(EOMONTH(TODAY(),0)),0)),0)

Hi! It works! Awesome! Thanks a lot!

My apologies, now I know.
Thank you.

Originally Posted by vlady

Welcome to the forum ANS.

Since this is your first thread and based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".
For the meantime I'll do it for you.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.