how to right this formula?

[smiler44]

how to write this formula?

for example sheet 2 column C range (rows) 5 to 30 has various first names in it. column F range (rows) 5 to 30 has various last names in.

I need to know how many times Jon appreas in column C range 5 to 30 when the surname in column F range 5 to 30 is Good

something along the lines of
= number of times jon appears in sheet2, column C range 5-30 when Good appears in sheet 2 column F range 5 to 30

I had something like
=sum((C5:C30="jon")*(F5:F30="good"))

How should the formula be written?

Thnak you
smiler44

 

[Brianwarnock]

Try sumproduct instead of sum

Brian

 

[DanWall]

are you using 2007 or 2003?

 

[DanWall]

The way I USED to do stuff like this, was just add in another column. (a hidden one somewhere) for example.... Column G.

the formula would array from g5 to g30, and would be like so.....
=if(and(Cx = "jon", Fx = "good"),1,0)
So all the rows with Jon and Good, would return a 1.
The rest would return 0.

And then, the total would be:
=sum(G5:G30)

 

[smiler44]

Using 2003
smiler44

 

[DanWall]

Right, then the easiest way by far, is to add another column next to the rows and follow the procedure I posted before.

I've tried this before, and pre-2007, it's the easiest way without getting into VBA.

 

[DanWall]

Well, actually! I just learned something!

Thank you Brianwarnock! Where were you 2 years ago when I was trying to figure this out?

=SUMPRODUCT((c5:c30="jon")*(f5:f30="good"))

Thank Brian for that.

 

[smiler44]

productsum did not work.

strange, depending on where I place this formula
=SUM(A5:A30 ="good", C5:C30="jon") I get a different answer.
put it in E31 I get #value!, put it in E30 I get 1, put it in E29 i get 2 and in E38 I get 0

so what exactly is my rubbish attempt at a formula asking?

smiler44

 

[smiler44]

ok I give up. not a clue whats going on.
I post or two ago I said to Brian's suggestion that sumproduct did not work. Danwall then posts and types the formula that I then copy and paste and it works. unless I'm missing something it's the same formula but with sumproduct instead of sum.

sorry Brian

may be it's time to call in a night and start again tomorrow. Thank you everyone for your help
smiler44

 

[DanWall]

smiler44.

If you are copying and pasting the CELL, it will make a relative formula, ie: shift the cell references relative to the original copied cell (hope this makes sense).

If you are entering the formula correctly, on ANY cell, then it will work regardless.

Attach the spreadsheet and let us have a look?

 

[smiler44]

Danwall, what ever was wrong, pasting the code from your post did the trick.
I went to use the formula today and have been told that the info I wanted to
retrieve may be in different rows next time as well as being different quantities
it may aslo be different products. Formulas are out, macros are in.
Hours wasted!

smiler44

 

[Brianwarnock]

Dan I haven't got 2007 but I have heard that the functionality of Sumif has been increased and that Sumproduct might be sidelined, however for all pre 2007 user Sumproduct is a gem.

Guide to sumproduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

This link is not available at the moment so I will attach a zip of the document.

Brian

 

[Brianwarnock]

Smiler you can use named references in sumproduct so it may still have a part to play.

Brian

 

[smiler44]

Brian,
I do not know. From my postings you probably know that I'm a bit of a novice. I have been told to shelve this part of the project and move on.
Now I'm on to yet another problem.
Smiler44

 

[speakers_86]

Sumproduct is great, but its awfully slow. You dont want hundreds of sumproducts in your workbook.

 

[DanWall]

Brian, the new function is
SUMIFS

It's the way forward.