excel

Rebecca,

Your case fits the category I call multiconditional count. COUNTIF can handle just one condition.

There are few ways to get what you want:

(a) Using DCOUNT, which is one of the database functions that are available within Excel;

(b) Using an array formula like the following that can handle your problem:

=SUM(('July '!N2:N82="VM")*('July '!R2:R82=0))

You need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER) to enter an array formula.

Note that if you enter this formula in some cell on the 'July ' sheet, you can shorten this array formula to:

=SUM((N2:N82="VM")*(R2:R82=0))

(c) Instead of using the array formula above, you can do the following:

In e.g., S2 enter: =(N2="VM")*(R2=0)

Copy down this formula up to row 82. You'll see lots of 0's and/or 1's in S2 to S82. Now, in S1 enter:

=COUNT(S2:S82)

This formula not only computes the same result (by using lots of cells instead of a sinle cell) as the one under (b) but also explains how that array formula works.

Aladin

I TRIED THE ARRAY FORMULA BUT I GET A #VALUE! IN THE CELL...IS THERE SOMETHING I'M MISSING....THANK YOU SO MUCH FOR RESPONDING......
=SUM(('July '!N2:N82="VM")*('July '!R2:R82=0))

I TRIED THE ARRAY FORMULA BUT I GET A #VALUE! IN THE CELL...IS THERE SOMETHING I'M MISSING....THANK YOU SO MUCH FOR RESPONDING......
=SUM(('July '!N2:N82="VM")*('July '!R2:R82=0))

Rebecca,

Activate the cell of the formula, go to the formula bar, hit CONTROL+SHIFT+ENTER at the same time. If you see braces (i.e. {}) around the formula, then the formula is entered as it should.

Aladin

==========

=SUM(('July '!N2:N82="VM")*('July '!R2:R82=0))

ALADIN,

I ALSO TRIED THE THIRD ONE =(N2="VM")*(R2=0) I DON'T SEE HOW THIS IS SUPPOSE TO WORK...BECUASUE IF I'M MULTIPLYING BY ZERO IT WILL ALWAYS BE ZERO HOW AM I CHECKING TO SEE IF THE "VM" IS THERE...iT LOOKS LIKE I'M SETTING THE SELL = TO VM.........I'M SORRY IF I SOUND CONFULSED.......I DON'T KNOW IF I CAN ATTACT THE FILE TO THIS...BUT I'M GOING TO TRY...........

CELLS N2:N82
PBX
1MB
T1
VM
T1 FRAME RELAY
ISDN BRI
ASSUME 9 CTX
VM
VM
VM
PBX
PBX
NA
CENTRANET
NA
VM
CALL FORWARD BUSY
CALL FORWARD BUSY
LONG DISTANCE
1MB
VM
T1 FRAME RELAY
ISDN BRI
T1 FRAME RELAY
ISDN BRI
T1 FRAME RELAY
ISDN BRI
TREATMENT CODES
ASSUME 9 CTX
IW
FRAME RELAY
TREATMENT CODES
VM
ISDN BRI
CALL FORWARD VARIABLE
CENTRANET & HUNTING
CALL FORWARD VARIABLE
1MB
56K SMDS
1MB
CENTRANET
CENTRANET
CENTRANET
CENTRANET
T1
T1 FRAME RELAY
ISDN BRI
CIRCUIT
CIRCUIT
CIRCUIT
CIRCUIT
CIRCUIT
CIRCUIT
CIRCUIT
HUNTING
VM
CIRCUIT
TREATMENT CODES
CIRCUIT
CIRCUIT
VM
VM

CELLS R2 : R82
0
0
0
0
0
0
-3
0
0
0
0
0
0
2
0
0
0
1
1
1
1
1
0
0
0
1
1
1
1
4
4
#VALUE!
0
#VALUE!
0
0
0
0
0
0
0
0
0
0
0
0
0
2
2
0
0
0
0
0
0
0
0
0
0
2
0
0
0
1
1
1
1
1
1
1
1
1
1
1
1
0
0
1
1
0
0

OF THE CELLS THAT EQUAL "VM" IN CELLS N2:N82 HOW MANY OF THEM IN THE CORRESPONDING CELLS OF R2:R82 HAD A VALUE OF 0.......

THAT'S WHAT I NEED TO DO.........

:Aladdin,

I did that and it came back with the Value error again........

Rebecca,

Rebecca,

I see 2 #VALUE! errors in column R. These will make trouble. We need to resolve that first. When resolved, you'll see that the array formula works. How come you have them there? Are there formulas in that column that compute those numbers? If so, please post that formula in the follow up.

Aladin

I ALSO TRIED THE THIRD ONE =(N2="VM")*(R2=0) I DON'T SEE HOW THIS IS SUPPOSE TO WORK...BECUASUE IF I'M MULTIPLYING BY ZERO IT WILL ALWAYS BE ZERO HOW AM I CHECKING TO SEE IF THE "VM" IS THERE...iT LOOKS LIKE I'M SETTING THE SELL = TO VM.........I'M SORRY IF I SOUND CONFULSED.......I DON'T KNOW IF I CAN ATTACT THE FILE TO THIS...BUT I'M GOING TO TRY........... PBX 1MB T1 VM T1 FRAME RELAY ISDN BRI ASSUME 9 CTX VM VM VM PBX PBX NA CENTRANET NA VM CALL FORWARD BUSY CALL FORWARD BUSY LONG DISTANCE 1MB VM T1 FRAME RELAY ISDN BRI T1 FRAME RELAY ISDN BRI T1 FRAME RELAY ISDN BRI TREATMENT CODES ASSUME 9 CTX IW FRAME RELAY TREATMENT CODES VM ISDN BRI CALL FORWARD VARIABLE CENTRANET & HUNTING CALL FORWARD VARIABLE 1MB 56K SMDS 1MB CENTRANET CENTRANET CENTRANET CENTRANET T1 T1 FRAME RELAY ISDN BRI CIRCUIT CIRCUIT CIRCUIT CIRCUIT CIRCUIT CIRCUIT CIRCUIT HUNTING VM CIRCUIT TREATMENT CODES CIRCUIT CIRCUIT VM VM 0 0 0 0 0 0 -3 0 0 0 0 0 0 2 0 0 0 1 1 1 1 1 0 0 0 1 1 1 1 4 4 #VALUE! 0 #VALUE! 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 0 0 0 0 0 0 0 0 0 0 2 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 0 0 1 1 0 0

That's OK. See the other post. (NT)

:Alladin,

The reason those error values are there is because in the actual database there is some information that doesn't fit to the formula we're using in row R. but the reason we wer o.k. with leaving it there is because we didn't need to capture that data...so there is no other way for me to compare those cells unless the data is clean.....??? i'm putting validations on the cells to make sure clean dat is entered from now on...but the formulas in the cells from r2 through v2
=NETWORKDAYS(A33,G33)-1 are calculating workdays.......and in some of the columns from a33 to g33 this is the data we have
NA NA NA NA NA NA NA
because for those products we don't need to use those columns...therefore we get the value error in the other cells.............

Rebecca, : I ALSO TRIED THE THIRD ONE =(N2="VM")*(R2=0) I DON'T SEE HOW THIS IS SUPPOSE TO WORK...BECUASUE IF I'M MULTIPLYING BY ZERO IT WILL ALWAYS BE ZERO HOW AM I CHECKING TO SEE IF THE "VM" IS THERE...iT LOOKS LIKE I'M SETTING THE SELL = TO VM.........I'M SORRY IF I SOUND CONFULSED.......I DON'T KNOW IF I CAN ATTACT THE FILE TO THIS...BUT I'M GOING TO TRY........... : CELLS N2:N82 : PBX : 1MB : T1 : VM : T1 FRAME RELAY : ISDN BRI : ASSUME 9 CTX : VM : VM : VM : PBX : PBX : NA : CENTRANET : NA : VM : CALL FORWARD BUSY : CALL FORWARD BUSY : LONG DISTANCE : 1MB : VM : T1 FRAME RELAY : ISDN BRI : T1 FRAME RELAY : ISDN BRI : T1 FRAME RELAY : ISDN BRI : TREATMENT CODES : ASSUME 9 CTX : IW : FRAME RELAY : TREATMENT CODES : VM : ISDN BRI : CALL FORWARD VARIABLE : CENTRANET & HUNTING : CALL FORWARD VARIABLE : 1MB : 56K SMDS : 1MB : CENTRANET : CENTRANET : CENTRANET : CENTRANET : T1 : T1 FRAME RELAY : ISDN BRI : CIRCUIT : CIRCUIT : CIRCUIT : CIRCUIT : CIRCUIT : CIRCUIT : CIRCUIT : HUNTING : VM : CIRCUIT : TREATMENT CODES : CIRCUIT : CIRCUIT : VM : VM : CELLS R2 : R82 : 0 : 0 : 0 : 0 : 0 : 0 : -3 : 0 : 0 : 0 : 0 : 0 : 0 : 2 : 0 : 0 : 0 : 1 : 1 : 1 : 1 : 1 : 0 : 0 : 0 : 1 : 1 : 1 : 1 : 4 : 4 : #VALUE! : 0 : #VALUE! : 0 : 0 : 0 : 0 : 0 : 0 : 0 : 0 : 0 : 0 : 0 : 0 : 0 : 2 : 2 : 0 : 0 : 0 : 0 : 0 : 0 : 0 : 0 : 0 : 0 : 2 : 0 : 0 : 0 : 1 : 1 : 1 : 1 : 1 : 1 : 1 : 1 : 1 : 1 : 1 : 1 : 0 : 0 : 1 : 1 : 0 : 0 : OF THE CELLS THAT EQUAL "VM" IN CELLS N2:N82 HOW MANY OF THEM IN THE CORRESPONDING CELLS OF R2:R82 HAD A VALUE OF 0....... : THAT'S WHAT I NEED TO DO.........

Aladdin,

What if I use the AND statement with to if's and then add the countif??? Will that work........

Rebecca :

:Aladdin, :

The reason those error values are there is because in the actual database there is some information that doesn't fit to the formula we're using in row R. but the reason we wer o.k. with leaving it there is because we didn't need to capture that data...so there is no other way for me to compare those cells unless the data is clean.....??? i'm putting validations on the cells to make sure clean dat is entered from now on...but the formulas in the cells from r2 through v2 =NETWORKDAYS(A33,G33)-1 are calculating workdays.......and in some of the columns from a33 to g33 this is the data we have NA NA NA NA NA NA NA because for those products we don't need to use those columns...therefore we get the value error in the other cells.............

Rebecca,

I think it's better to prevent this type of errors. In the meantime, you can use the alternative (c). You need then replace the COUNT formula with the following COUNTIF formula:

=COUNTIF(S2:S82,1)

This will produce the required count.

Aladin

What if I use the AND statement with to if's and then add the countif??? Will that work........

IF YOU ENTER IN S2 THE FORMULA

=IF(AND(N2="VM",R2=0),1,0),

WHICH IS EQUIVALENT TO THE FORMULA OF THE ALTERNATIVE (c), tha is,

=(N2="VM")*(R2=0) [ Try this; it will make you understand the array formulas! ]

AND USE IN S1

=COUNTIF(S2:S82,1)

YOU'LL ALSO GET THE DESIRED COUNT.

ALADIN