Excel Formula Help...

oHUTCHYo · 21 Jan 2013 at 15:24

My Google Fu is failing. OCUK - can you help?

I have a list of sales quotations issued by my team. As well as other info, each line recorded has a date (column A) (Format Example: 29/05/2012) and in another column a status, i.e. "Won"

I'm currently using this formula;

Code:

=SUMPRODUCT(--(TEXT(Table3[[#All],[DATE]],"MMYYYY")="012013"))

Which counts how many quotations have been done in a given month, effectively it just counts the occurences of "MMYYYY" to a given request, i.e. 052012 for May 2012

I now need to count how many of those counted in a particular month have a status of "Won", in column H.

Suggestions?

mattx2 · 21 Jan 2013 at 15:40

You can probably do this with a countif formula but would it not be quicker and easier to use a pivot table ?

oHUTCHYo · 21 Jan 2013 at 15:46

mattx2 said:
You can probably do this with a countif formula but would it not be quicker and easier to use a pivot table ?

Hi Matt, I'd prefer a formula if possible as other less Excel savvy users will be using this document too.

oHUTCHYo · 21 Jan 2013 at 15:47

Code:

=SUMPRODUCT(--(TEXT(Table3[[#All],[DATE]],"MMYYYY")="012013"))

Can I somehow use the above then add a countif amongst the formula to only return those which are then marked as "Won" in an adjacent column?

mattx2 · 21 Jan 2013 at 16:00

You should've able to add *(your range="won") onto the formula above. If you can't get it to work I'll have a look when I finish work

oHUTCHYo · 21 Jan 2013 at 16:03

mattx2 said:
You should've able to add *(your range="won") onto the formula above. If you can't get it to work I'll have a look when I finish work

Code:

=SUMPRODUCT(--(TEXT(Table3[[#All],[DATE]],"MMYYYY")="072012"))*(Table3[STATUS]="WON")

Returns 0. When in fact it should return 11 which are "Won".

wesimmo · 21 Jan 2013 at 16:08

=SUMPRODUCT(--(TEXT(Table3[[#All],[DATE]],"MMYYYY")="072012"),--(Table3[STATUS],"WON"))

I don't think you even need to do all the text malarky with the date either, but if that bit works for you already then leave it.

With sumproduct you can add as many criteria as you like, separated by commas, always adding --(RANGE,criteria).

The -- basically means it resolves the array into a series of 1s and 0s (TRUE or FALSE).

oHUTCHYo · 21 Jan 2013 at 16:10

Thanks Wes, though its saying the formula is invalid?

wesimmo · 21 Jan 2013 at 16:10

If that doesn't work, Trust me an example of the .xls, spoof the actual data if you want, and i'll sort it for you. It's difficult to check the syntax is right in a forum text box!

Edit: BTW i edited my original answer twice as i realised i had missed commas and got brackets in the wrong place, does my final version not work still?

oHUTCHYo · 21 Jan 2013 at 16:17

wesimmo said:
If that doesn't work, Trust me an example of the .xls, spoof the actual data if you want, and i'll sort it for you. It's difficult to check the syntax is right in a forum text box!

Edit: BTW i edited my original answer twice as i realised i had missed commas and got brackets in the wrong place, does my final version not work still?

Thanks buddy, tried again but no luck.

Having problems with the trust system, can you perhaps email me so I can respond? ianh AT exmesh DOT co.uk

Thanks again

wesimmo · 21 Jan 2013 at 16:21

Damn it, just tried some dummy data on my own PC.

=SUMPRODUCT(--(TEXT(Table3[[#All],[DATE]],"MMYYYY")="072012"),--(Table3[STATUS]="WON"))

I'd not used the ="WON", i'd put ,"Won"

Does that work?

oHUTCHYo · 21 Jan 2013 at 16:23

..now gives 0 lol

wesimmo · 21 Jan 2013 at 16:24

I'll email you!

oHUTCHYo · 21 Jan 2013 at 16:50

All sorted guys, thanks again

wesimmo · 21 Jan 2013 at 16:57

TBF, you were always going to struggle, as per the email you were not only fighting getting the formula right, but being able to drag it across all the analysis would never work due to one of the "quirks" of Excel....