Excel Formula Help

[DeeJay]

Can any "Excel Nut" help me with the formulae for the following ?
I am a novice with Excel & cannot work out how to obtain what I am after, I have tried Help & looked in the Excel User Guide book with out success.
I only have office 97 at this stage, can it do what I want ?
I can get for example =SUMIF(C3:C530,2,E3:E530) this gives me the sum of the value of cells in Col E3 to E530 only IF corresponding cells in Col C3 to C530 value is 2. But if I also want to add AND (IF B3:B530,4) AND (IF D3:D530,3) no matter how I bracket or colon or coma the formula it will not accept the formula saying error or too many functions.
So what I am after in above example is to sum the value of the cells in Col E only when value in corresponding cell of Col C is 2 but only when value in corresponding cell of Col B is 4 and the value of the corresponding cell in Col D is 3.

Similarly for COUNT & COUNTIF eg =COUNTIF(F3:F500,6) gives me the number of cells in Col F3 to F500 with a value of 6. But can I add AND (IF N3:N500,12) and (IF E3:E500,3)
So what I am after in above example is to count the number of times 6 occurs in Col F but only when corresponding cell in Col N is 12 and the corresponding cell in Col E is 3.

Any assistance much appreciated
With Thanks

[becareful]

Deejay,

I think the following will do what you want.

For first example (sum of column E when value in B is 4, C is 2 and D is 3).
I would add an extra 2 columns (I will assume F and G). In Column F of the first row of your data (ie cell F3) put in the following forumula:
=IF(B3=4,IF(C3=2,IF(D3=3,1,0),0),0)
This will give you a value of 1 in column F if the three values are as specified or 0 if they are not. Copy this forumla down the whole column. Now in cell G3 put in the formula:
=E3*F3
Again copy this down the whole of column G. This will give you the value of column E only if the criteria are as specified, otherwise it will give a 0.

Now simply sum columns F and G. Column F will give you the count of the number of matches and column G will be the sum you are after.

Hope this helps - if you want me to email you an example post your email address and I will send it to you (or send me an email at the tipping competition address.


"Becareful"

[DeeJay]

Becareful
Thanks for the response.
I will try it later tonight & see if that does it.
I am fairly new to this forum so could you post the tipping comp email address ( I will take you up on your offer of an example to make sure I get it right )
With Thanks

[becareful]

tipping@tlcbc.com.au

[DeeJay]

Many thanks to Becareful & Equine Investor for their responses to my query.

With the info available at http://www.mrexcel.com/articles.shtml (a link Equine Investor put me on to) I have discovered that Excel can most certainly do what I required & so much more!
If anyone else needs any Excel tips or help I strongly suggest you go to this site, it will save you from hours of frustration. In particular for formula help use the link above & click the heading Formulas in Excel, There are reams of problem solving & the site also has a forum to ask questions if you can't find what you need.
For WORD Help See TheWordExpert.com
For ACCESS Help See http://www.technoweenies.ca

The answer to my own enquiry from original posting above is for SUMIF question
=SUM(($C$3:$C$530=2*($B$3:$B$530=4) )*($D$3:$D$530=3)*($E$3:$E$530))
& for COUNTIF question
=SUM(($F$3:$F$500=6)*($N$3:$N$500=12)*($E$3:$E$500=3)*1)
And you have to hold down the Ctrl and the Shift keys, and then hit Enter. (see quote below)

The $ symbols lock in the numerical values if you drag & drop. If you want to drag & auto fill remove all of the $ symbols.
Hope this helps someone else
Regards
DeeJay

The following is a quote from the Mr Excel web page about entering the above formula.

"Here is the secret: After you type a CSE formula, you have to hold down the Ctrl and the Shift keys, and then hit Enter.
You cannot just exit the cell with a click of an arrow key. Even if you get the formula right and hit the enter key, Excel gives you the totally non-user friendly "VALUE!" error. It doesn't say, "Wow – that is beautiful. You are 99.1% of the way there," which you probably were.
After you successfully enter one of these and look at it in the formula bar, you should have curly braces around the formula. You never enter the curly braces yourself. Hitting Ctrl+Shift+Enter puts them there."