How do I compare two values to calculate a total?
 

I'm trying to compare two columns, and count it as "1" in a total, if both
cells meet individual criteria, to see if we have enough players for a
softball game.

Column D states whether the player is M of F, and column G lists a Y or N as
to whether that player is available for that specific game. So, I want to
count all the cells where D="F" and G="N", but only when both conditions are
true. This is for range 2:22 in both columns. Seemed simple enough when I
started, but I'm doing something wrong.

How do I compare two values to calculate a total?
 

=SUMPRODUCT(--(D2:D200="F"),--(G2:G200="N"))

=SUMPRODUCT(--(D2:D200=H1),--(G2:G200=H2))

where H1 holds F and H2 N

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"lori_tig" wrote in message
...
I'm trying to compare two columns, and count it as "1" in a total, if both
cells meet individual criteria, to see if we have enough players for a
softball game.

Column D states whether the player is M of F, and column G lists a Y or N
as
to whether that player is available for that specific game. So, I want to
count all the cells where D="F" and G="N", but only when both conditions
are
true. This is for range 2:22 in both columns. Seemed simple enough when
I
started, but I'm doing something wrong.

How do I compare two values to calculate a total?
 

Thanks!!

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(D2:D200="F"),--(G2:G200="N"))

=SUMPRODUCT(--(D2:D200=H1),--(G2:G200=H2))

where H1 holds F and H2 N

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"lori_tig" wrote in message
...
I'm trying to compare two columns, and count it as "1" in a total, if both
cells meet individual criteria, to see if we have enough players for a
softball game.

Column D states whether the player is M of F, and column G lists a Y or N
as
to whether that player is available for that specific game. So, I want to
count all the cells where D="F" and G="N", but only when both conditions
are
true. This is for range 2:22 in both columns. Seemed simple enough when
I
started, but I'm doing something wrong.

How do I compare two values to calculate a total?
 

Hi,

While sumproduct is a better solution, another solution is array formulas
(Ctrl+Shift+Enter)

=sum(if((D2:D22="F")*(G2:G22="N"),1,0))

Regards,

Ashish Mathur

"lori_tig" wrote:

I'm trying to compare two columns, and count it as "1" in a total, if both
cells meet individual criteria, to see if we have enough players for a
softball game.

Column D states whether the player is M of F, and column G lists a Y or N as
to whether that player is available for that specific game. So, I want to
count all the cells where D="F" and G="N", but only when both conditions are
true. This is for range 2:22 in both columns. Seemed simple enough when I
started, but I'm doing something wrong.