Newbie Formula Question

[Madsocks]

Hi folks,

I'm a bit of a newb when it comes to Excel formulas. I'm currently in a MI reporting role and a lot of my work involves spreadsheets and I've started playing around with formulas but I just can't seem to get a specific formula and i'm not sure whether one can be used so wondering if any of you fine folk can help.

I have a spreadsheet in work and have set up two tabs; one is called pipeline where the data is, and my other sheet is where my calculations will be and data validation for the drop down list (call it sheet1)

I'm looking to use 3 specific pieces of information into a formula to work out a monetary value for a group of opportunities to aid with my MI reporting. It's a rather large spreadsheet with 917 rows and columns from A-AG but the 3 column I'm focusing on at the moment is as follows:

Column G - contains a data validation filter with 3 drop down choices (won on supply, won renewal, won pending switch)

Column R - contains a monetary value in £ for each opportunity (there are other columns similar but I'm just focusing on this one for now)

Column AD - contains a date

What I'm looking at doing is trying to obtain a total value for everything in column R that is under won on supply on column G but after the date of 01/04/17 that is shown in column AD.

The formula I was tinkering with is below (located in sheet1) after reading numerous web pages on the subject:

=SUMIF(Pipeline!AD4:AD917,">"&DATE(2017,3,31),Pipeline!R4:R917)&" "&SUMIF(Pipeline!G4:G917,"*"&A2&"*",Pipeline!R4:R917)

The A2 part is where the wording Won On Supply is located in sheet1 where the calcs/data validation info is. That formula brings out two values separated by a space but doesn't give me the actual figure I'm looking for as from what I have read the above is just two formulas bringing back two different sets of values.

I hope the above really makes sense to someone. I've been trying to wrack my brain and come up with a few figures but I can't seem to come up with the correct one.

Please help as it's driving me absolutely crazy trying to work it out!

Thanks

LJ

 

[NARAYANK991]

Hi ,

Try this :

=SUMIFS(Pipeline!$R$4:$R$917,Pipeline!$AD$4:$AD$917,">"&DATE(2017,3,31),Pipeline!$G$4:$G$917,Sheet1!$A$2)

 

[Madsocks]

Hi ,

Try this :

=SUMIFS(Pipeline!$R$4:$R$917,Pipeline!$AD$4:$AD$917,">"&DATE(2017,3,31),Pipeline!$G$4:$G$917,Sheet1!$A$2)

Hi Narayank991,

Thanks very much for your reply. I've tried the formula but unfortunately it's just bringing back a value of zero.

 

[NARAYANK991]

Hi ,

Have you tried applying a filter on your data in the Pipeline tab , putting a filter so that dates prior to April 1 , 2017 are not considered , and column G has only won on supply ?

 

[thisoldman]

Hi Madsocks,

There's a little confusion from your original post. You write that you want the sums for dates after 2017-01-04 but your formula is written for dates after 2017-03-31. Please clarify.

Are the dates in column AD Excel dates, that is, are they numbers? Or are they text? Please report the results of
=ISNUMBER(AD4)

 

[Madsocks]

Hi Madsocks,

There's a little confusion from your original post. You write that you want the sums for dates after 2017-01-04 but your formula is written for dates after 2017-03-31. Please clarify.

Are the dates in column AD Excel dates, that is, are they numbers? Or are they text? Please report the results of
=ISNUMBER(AD4)

Hi thisoldman,

Apologies, it's figures for dates after 31 March 2017 so that it includes 1 April 2017 as it's the start of the financial year.

In column AD, the dates are excel dates, format as "date 01/04/17". No text located within the cells.

Thanks

LJ

 

[Madsocks]

Hi ,

Have you tried applying a filter on your data in the Pipeline tab , putting a filter so that dates prior to April 1 , 2017 are not considered , and column G has only won on supply ?

Hi Narayank991,

I have, but there are opportunities prior to 1st April 2017 that we need to monitor as well so I wouldn't be able to keep a permanant filter against the date.

With column G, I'm trying to get the values for all 3 of the statuses; won on supply, won renewal and won pending switch so cannot focus on just one. Plus the spreadsheet is shared by all our business managers so they require access to all the unfiltered information.

It's a bloody nuisance, this spreadsheet!

 

[NARAYANK991]

Hi ,

My point in asking was to know what the formula should return.

If you apply the filter , do you see any records displayed ?

Are you sure the text won on supply is exactly that , without any leading / trailing spaces ?

 

[Madsocks]

Hi Narayank991,

The text is exactly the same as what it shows "Won - On Supply". The information the formula should return should be a revenue figure in £ for all accounts under "Won - On Supply" with a date on or after 1st April 2017. I've done the filters but it still doesn't seem to be working. I get the feeling that I'm just going to have to manually do this at the end of every month, which is going to be a veritable nightmare.

 

[NARAYANK991]

Hi ,

No wonder !

The text you have posted just now shows this :

Won - On Supply

The formula I posted shows the text as :

Won On Supply

Change the formula to use the exact same text that is available in the Data Validation dropdown , and you should get your results.