smiler44
03-24-2009, 04:11 AM
I have this formula that I wish to modify but all my attempts have failed.
The formula is:-
=SUMPRODUCT(((C1:C10)<>"Jon")*(F1:F10="Regular")+(F1:F10="Expatriate"))
It counts the number of times Regular or Expatriate appear in column F when column C in not Jon.
I want to add to the forumula to include D1:D10 = "JFC"
It should then end up counting "not jon but is JFC and Regular" and is "not jon but is JFC and Expatriate"
smiler44
smiler44
03-24-2009, 04:58 AM
with the help of a posting by Brianwarnock I have put together:-
=SUMPRODUCT((C1:C10<>"jon")*(D1:D10="JFC")*(F1:F10="Regular"))+SUMPRODUCT((C1:C10<>"jon")*(D1:D10="JFC")*(F1:F10="Expatriate"))
Thank you again Brian
smiler44
Brianwarnock
03-24-2009, 05:09 AM
I think that your bracketing is incorrect in your first formula
=SUMPRODUCT(((C1:C10)<>"Jon")*((F1:F10="Regular")+( F1:F10="Expatriate")))
and now you just add (d1:d10="JFC)*
=SUMPRODUCT(((C1:C10)<>"Jon")*(D1:D10="jfc")*((F1:F10="Regular")+( F1:F10="Expatriate")))
BTW you need to disable smilies in text when you have arrays on col D
Brian
smiler44
03-24-2009, 10:25 AM
Brian,
just been reading some more of the word document.
I have come up with two formulas
=SUMPRODUCT((LEFT(B1:B10,2)="go")*1,(LEFT(C1:C10,2)="be")*1,(LEFT(D1:D10,2)="de")*1)
and
=SUMPRODUCT((B1:B10="go")*1,(C1:C10="be")*1,(D1:D10="de")*1)
I know these do not use same columns and words to search but in a new workbook these seem to work. Also they make some of my other formulas look exceedingly long.
The top one seems to deal with my attempt to use a wildcard.
I'll try these tomorrow and if they do indeed work I will have to consider replacing a long working formula with a short working formula.
smiler44
Brianwarnock
03-24-2009, 11:55 AM
Yes Smiler that will work to count the number of rows containing all 3 criteria, I had to do a quick think as I'm a unary operator man myself but you may not be that far down the document yet and I seem to remember no real advantage for one way or another being stated, its just my old brain stuck in a rut.
You are becoming adept at this and I am pleased that you are so willing to learn, too many posters don't seem to be.
Brian