Excel Help (Sum Product)

oHUTCHYo

oHUTCHYo

oHUTCHYo

Associate
Joined
7 Jun 2005
Posts
2,428
Location
North East
Hi Guys,

Really need some help as I'm failing badly. I already posted this a while back and folk tried to help, I've gave up after trying all this time.

This is not just a matter of a time saving spreadsheet, but a battle with another one of my peer managers who want's this done manually as he is such a technophobe, I'm trying to convince him this can be done easily but am failing.

I have provided a link. I have sumproduct working well on the Q2 tab. However as there is so much criteria that I need in the formulas in Q3 that searches in Q1, I'm not doing well.

Can someone be kind enough to help and perhaps even just complete the first row for me so I can understand what is required? Note the criteria required; by month, by initial, by value bracket but then in the next section by month, by initial, by value bracket and only IF won.

Very hard :(

http://www.megafileupload.com/en/file/438366/UK-Sales-Quotations---Dashboard-xlsx.html
 
Okay, I think this is what you're looking for, for the left side at least.

You'll notice that I've changed the month to a number instead of a string which makes things a bit neater, I'd always advise people to do this.

I've no idea how you know if someone has won or not but it should be a simple addition to the countif formula.

Edit: You probably need a link :o

http://sdrv.ms/16O9GiH
 
Last edited:
Antar Bolaeisk said:
Okay, I think this is what you're looking for, for the left side at least.

You'll notice that I've changed the month to a number instead of a string which makes things a bit neater, I'd always advise people to do this.

I've no idea how you know if someone has won or not but it should be a simple addition to the countif formula.

Edit: You probably need a link :o

http://sdrv.ms/16O9GiH
Click to expand...

Sorry to bother you pal, but can you show me a won one too on the right section? Column N on Q1 shows the status, some are marked as "Won".
 
I'm not at my computer at the moment but it should literally be a case of sticking

,Q1!$N:$N,"Won"

At the end of the countifs() formula. If you don't get it working by the time I get back I'll knock something together.

Also, you should really remove the full column references as this is generally considered "A Bad Thing", and you already have the dynamic ranges set anyway. I was just too lazy last night.
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom