Excel Formula Help...

Associate
Joined
7 Jun 2005
Posts
2,428
Location
North East
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?
 
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?
 
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".
 
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 :)
 
Back
Top Bottom