Counting a non-number value with a condition

**servant117** · 05-05-2008, 04:26 PM

Help,

Here is my problem:

In column A4:A244 I have the numbers 1,2,3 or 4

In another column D4:D244 I have 6 different values such as, A, B, C, D, E , F that they could have chosen.

I want to find out how many 1s in Column A chose A in column D?

I have tried the countif function, sumproduct function but none work.

Thanks for your help.

**VBA Noob** · 05-05-2008, 04:29 PM

Try

=SUMPRODUCT((A4:A244=1)*(D4:D244="A"))

VBA Noob

**servant117** · 05-05-2008, 04:55 PM

Thanks a million!

**servant117** · 05-07-2008, 06:19 PM

Here is my problem:

In column A4:A244 I have the numbers 1,2,3 or 4

In another column D4:D244 I have 6 different values such as, A, B, C, D, E , F that they could have chosen.

I want to find out how many 1s in Column A chose A in column D?

I have tried the countif function, sumproduct function but none work.
I solved the ABOVE

Thanks for your help.
{=AVERAGE(IF(M64:M123<>0, M64:M123,"")}

Now I want to do the same thing with the Above is the formula.
Can anyone help please?

**daddylonglegs** · 05-07-2008, 06:29 PM

I'm not clear what you're asking here, how is the AVERAGE formula relevant?

If this question isn't related to your first question you should start a new thread as per the forum rules

**servant117** · 05-08-2008, 10:46 AM

{=AVERAGE(IF(M64:M123<>0, M64:M123,"")}

Please look at the above formula. It is a formula where the question was asked "Was the chaplain available when you needed him?" Possible answers was -3 -2 -1 0 1 2 3.

Here is what I want to do:

In column A4:A244 I have the numbers 1,2,3 or 4.

I want to use the Average formula above but only average those who are 1 or 2 or 3 or 4 from column A4:244 above. I hope this makes sense.
Thanks

**daddylonglegs** · 05-08-2008, 11:43 AM

No, sorry, I still don't get it. How does the range M64:M123 relate to the range A4:A244, One has 60 cells the other 240?

Which cells exactly do you want to average.

Perhaps you could give a pared down example, with expected results?

**servant117** · 05-08-2008, 12:15 PM

=SUMPRODUCT((A4:A244=1)*(D4:D244="A"))

This above formula only counts the As in range d4:d244 from those who are 1s in a4:a244

I want to basically do the same but I only want to average those who are 1s in column "A" who have put down values in column m where the 0 is not counted.

{=AVERAGE(IF(M64:M123<>0, M64:M123,"")}

I want to do the above(the person who created this above average formula simply average the cells that have a 1 in them.

I want to make it chose the 1s from column a4:a244 and average them not counting the 0 like above.

column a column m
1 1
1 -2
1 0
1 3
2 3
2 2
2 0
3 -1
3 -2
3 -3
4 3
4 2
4 3

I want to chose the 1s in column A and average thier responses in column M not counting the 0 values.
Thanks