Countif multiple variables

Thanks:  0
Likes:  0

1. ## Countif multiple variables

Hey all, I've been struggling with this formule for some time.
I'm inventarising things and would like a formula that can tell me how many computers should be updated.

In B2 to B260 are MS Office versions, C2 - C260 Adobe Reader, D2 - D260 Internet Explorer. Now I would like to know how many computers are not fully updated.

=MAX(B2:D260) did not help, because there are computers with bad office version and good Adobe Reader, but also vice versa. =Countif didn't do the job too, because it only saw computers that had all three programs outdated.

Can you help with this MrExcel forum? Thanks in advance.

2. ## Re: Countif multiple variables

Here is a formula that will tell you the number of machines that are up to date
assuming Win7, Adobe9 and IE8 are the standards. Note this is an array formula that must be entered using Control+Shift+Enter.

Sheet1

 A B C D 1 Machine MS OS Adobe IE 2 A XP 8 7 3 E Win7 9 8 4 B XP 9 8 5 C Win7 7 8 6 D Win7 8 8 7 1

 Cell Formula D7 {=SUM((B2:B6="Win7")*(C2:C6=9)*(D2:D6=8)*(A2:A6<>""))}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

3. ## Re: Countif multiple variables

Dear Alansidman,

Tried your formule and it works just fine in the test document, but in my own one it doesn't. Mind me uploading ( a part ) of it so you can see what you can do? Thanks in advance

 Hardware in TOPdesk Office Version Adobe Reader Internet Explorer Team Leader BAKLT5201 2003 7 8 name1 BAKLT5069 2000 9 8 name2 name3 BAKLT5272 2000 9 8 name4 BAKLT5271 2003 9 6 name5 BAKLT5273 2003 7 8 name6 BAKLT5036 2003 9 8 name7 SLALT7367 2000 9 6 name8 BARLT5276 2003 9 8 name9 x x x x name10 x x x x name11 x x x x name12 x x x x name13 x x x x name14 x x x x

4. ## Re: Countif multiple variables

What did not work? Did you use Control+Shift+Enter to enter the formula in the cell you are looking to populate with the answer?

5. ## Re: Countif multiple variables

Took your data and tried two different formulae on it and it comes out for me.

Sheet2

 A B C D E 1 Machine Hardware Office Adobe IE 2 TeamLeader BAKLT5201 2003 7 8 3 name1 BAKLT5069 2000 9 8 4 name2 5 name3 BAKLT5272 2000 9 8 6 name4 BAKLT5271 2003 9 6 7 name5 BAKLT5273 2003 7 8 8 name6 BAKLT5036 2003 9 8 9 name7 SLALT7367 2000 9 6 10 name8 BARLT5276 2003 9 8 11 2 12 2

 Cell Formula E11 {=SUM((C2:C10=2003)*(D2:D10=9)*(E2:E10=8)*(A2:A10<>""))} E12 {=SUM(IF(\$D\$2:\$D\$10=9,IF(\$E\$2:\$E\$10=8,IF(\$C\$2:\$C\$10=2003,1,0),0),0))}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

6. ## Re: Countif multiple variables

This works perfectly, thanks a lot!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•