Excel 2007 - Using IF Statements 1

[ladyck3]

Hi my saviours

Please help me to learn a new aspect of an IF statement. The only way I can learn this and understand is when I have practical application to reference. This is such the case and I get to learn something new! I'm EXCITED!

I have a master file that shows a list of articles by company and by Device ID ... There may be 50 articles for XYZ Company and Device ID ABC-123

I then have a master sheet that shows the contents basically. I have a column that lists how many articles are contained on the second sheet for this Company/Device.

I think I use an If/Then statement but have never done that before, and or "Match" which I don't understand...


Does this make sense? I want to know how many times Company/Device ID appears on the full list. Maybe its Index? Which I am also unclear of.... or heavens... maybe a new function all together?

Please help, as always I am forever in your debt!


ladyck3
aka: Laurie

 

[zelgar]

I think you're interested in the COUNTIF & COUNTIFS commands

If you're wanting to know how many times something occurs in a range (e.g., how many time Company X is listed in a range, you would use COUNTIF =COUNTIF(B2:B500,"Company X")

If you're interested in knowing when two or more things happend simutaneously, use the COUNTIFS command (e.g., Company X and Device ABC, =COUNTIFS(B2:B500,"Company X",C2:C500,"Device ABC")

 

[ladyck3]

Ok Countifs... I just learned that existed a month or two ago but didn't think it would apply here...

So this is what I did with what you told me:

=COUNTIFS(Articles!C:C,A2,D,B2)

A2 being the Company Name which will differ
B2 being the Device ID which will differ

I'm using the Cell References so as to not have to manually enter this information and for the Company, the data is on the Articles worksheet in column C, and Device in column D

(Oh... Company=C and Device=D... I didn't even plan that LOL)

It gave me a result of 0 and the response should be 67.

Now the reason I have to use both criteria is that one device might be used by multiple companies so I need the Company and device to match the data on the index page, and then count how many times it appears.

(I hope I'm explaining this well enough)

I tried a sum of two counta but that was no good obviously the results was skewed bad!

I appreciate the point in the right direction, I'll play some more and also keep vigil on the forum.


ladyck3
aka: Laurie

 

[ladyck3]

OO OOO OOOOOO I got it, silly me

=COUNTIFS(Articles!C:C,A2,Articles!D,B2)

I forgot to enter the second instance of the spreadsheet ARTICLES

WOO HOOO IT WORKS!!


I just get SO excited when things work out and I figure out what I did wrong in the first place.

Thank you SO MUCH for your guidance, it was must less painful than I thought it would be!!



ladyck3
aka: Laurie

 

[SkipVought]

Just in case you did not notice...

Start typing in a cell...
[tt]
=cou
[/tt]
and you see a popup window that has a list of ALL the COUNT... functions and COUP... functions and furthermore has a brief description of any selected (you can scroll up & down with the ARROW keys). If you scroll down to the COUNTIFS function and then TAB, it will COMPLETE that function with the OPEN PARENTHESES like...
[tt]
=COUNTIFS(
[/tt]
At this point you will see just below the formula in your cell another prompt like...
[tt]
=COUNTIFS(
[highlight]COUNTIFS(criteria_range1,criteria1,...[/highlight]
[/tt]
As you enter each argument and COMMA the BOLD emphasis move to then next argument prompt.

Neat, huh!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

Glad to help.