software

I need to find the total if conditions in two columns are met. I'm doing a
school lottery for enrollment and need to see how many students met the
criteria quot;yesquot; in a specific grade level quot;1quot;.

Example:

Column A Column B
Yes 1
3
Yes 1
1

I'm looking for the total number of times yes and 1 are in the same row.
Here it is 2 times.

Thanks for any help anyone can give me.

Hi!

Try this:

=SUMPRODUCT(--(A1:A100=quot;Yesquot;),--(B1:B100=1))

Biff

quot;Agallagherquot; gt; wrote in message
...
gt;I need to find the total if conditions in two columns are met. I'm doing a
gt; school lottery for enrollment and need to see how many students met the
gt; criteria quot;yesquot; in a specific grade level quot;1quot;.
gt;
gt; Example:
gt;
gt; Column A Column B
gt; Yes 1
gt; 3
gt; Yes 1
gt; 1
gt;
gt; I'm looking for the total number of times yes and 1 are in the same row.
gt; Here it is 2 times.
gt;
gt; Thanks for any help anyone can give me.
Biff,

Thanks. This worked however now I would like to place the totals on a
separate tab in the same worksheet. What additional information do I need to
add to this string. Below is what I tried but I received a #VALUE! error.

=SUMPRODUCT(--('Current Students'!K2:K1000=quot;YESquot;),--('Current
Students'!N2:N1000=1))

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; Try this:
gt;
gt; =SUMPRODUCT(--(A1:A100=quot;Yesquot;),--(B1:B100=1))
gt;
gt; Biff
gt;
gt; quot;Agallagherquot; gt; wrote in message
gt; ...
gt; gt;I need to find the total if conditions in two columns are met. I'm doing a
gt; gt; school lottery for enrollment and need to see how many students met the
gt; gt; criteria quot;yesquot; in a specific grade level quot;1quot;.
gt; gt;
gt; gt; Example:
gt; gt;
gt; gt; Column A Column B
gt; gt; Yes 1
gt; gt; 3
gt; gt; Yes 1
gt; gt; 1
gt; gt;
gt; gt; I'm looking for the total number of times yes and 1 are in the same row.
gt; gt; Here it is 2 times.
gt; gt;
gt; gt; Thanks for any help anyone can give me.
gt;
gt;
gt;

Biff,

Tried again and it worked. Thanks so much for your help. You have made my
life much easier. Will this same function work if the second product
(B1:B100=1) is text not numeric? Example: (B1:B100=KG)? or do I need to have
quot; around the KG as quot;KGquot;?

quot;Agallagherquot; wrote:

gt; Biff,
gt;
gt; Thanks. This worked however now I would like to place the totals on a
gt; separate tab in the same worksheet. What additional information do I need to
gt; add to this string. Below is what I tried but I received a #VALUE! error.
gt;
gt; =SUMPRODUCT(--('Current Students'!K2:K1000=quot;YESquot;),--('Current
gt; Students'!N2:N1000=1))
gt;
gt; quot;Biffquot; wrote:
gt;
gt; gt; Hi!
gt; gt;
gt; gt; Try this:
gt; gt;
gt; gt; =SUMPRODUCT(--(A1:A100=quot;Yesquot;),--(B1:B100=1))
gt; gt;
gt; gt; Biff
gt; gt;
gt; gt; quot;Agallagherquot; gt; wrote in message
gt; gt; ...
gt; gt; gt;I need to find the total if conditions in two columns are met. I'm doing a
gt; gt; gt; school lottery for enrollment and need to see how many students met the
gt; gt; gt; criteria quot;yesquot; in a specific grade level quot;1quot;.
gt; gt; gt;
gt; gt; gt; Example:
gt; gt; gt;
gt; gt; gt; Column A Column B
gt; gt; gt; Yes 1
gt; gt; gt; 3
gt; gt; gt; Yes 1
gt; gt; gt; 1
gt; gt; gt;
gt; gt; gt; I'm looking for the total number of times yes and 1 are in the same row.
gt; gt; gt; Here it is 2 times.
gt; gt; gt;
gt; gt; gt; Thanks for any help anyone can give me.
gt; gt;
gt; gt;
gt; gt;

If the logical test value is TEXT, yes, you need to enclose it in quotes:

(B1:B100=quot;KGquot;)

If the logical test value is numeric DO NOT use quotes.

A better way to do this would be to use cells to hold the criteria and then
refer to those cells:

A1 = Yes
B1 = 1 or KG (or whatever)

=SUMPRODUCT(--(A5:A100=A1),--(B5:B100=B1)

Biff

quot;Agallagherquot; gt; wrote in message
...
gt; Biff,
gt;
gt; Tried again and it worked. Thanks so much for your help. You have made
gt; my
gt; life much easier. Will this same function work if the second product
gt; (B1:B100=1) is text not numeric? Example: (B1:B100=KG)? or do I need to
gt; have
gt; quot; around the KG as quot;KGquot;?
gt;
gt; quot;Agallagherquot; wrote:
gt;
gt;gt; Biff,
gt;gt;
gt;gt; Thanks. This worked however now I would like to place the totals on a
gt;gt; separate tab in the same worksheet. What additional information do I
gt;gt; need to
gt;gt; add to this string. Below is what I tried but I received a #VALUE!
gt;gt; error.
gt;gt;
gt;gt; =SUMPRODUCT(--('Current Students'!K2:K1000=quot;YESquot;),--('Current
gt;gt; Students'!N2:N1000=1))
gt;gt;
gt;gt; quot;Biffquot; wrote:
gt;gt;
gt;gt; gt; Hi!
gt;gt; gt;
gt;gt; gt; Try this:
gt;gt; gt;
gt;gt; gt; =SUMPRODUCT(--(A1:A100=quot;Yesquot;),--(B1:B100=1))
gt;gt; gt;
gt;gt; gt; Biff
gt;gt; gt;
gt;gt; gt; quot;Agallagherquot; gt; wrote in message
gt;gt; gt; ...
gt;gt; gt; gt;I need to find the total if conditions in two columns are met. I'm
gt;gt; gt; gt;doing a
gt;gt; gt; gt; school lottery for enrollment and need to see how many students met
gt;gt; gt; gt; the
gt;gt; gt; gt; criteria quot;yesquot; in a specific grade level quot;1quot;.
gt;gt; gt; gt;
gt;gt; gt; gt; Example:
gt;gt; gt; gt;
gt;gt; gt; gt; Column A Column B
gt;gt; gt; gt; Yes 1
gt;gt; gt; gt; 3
gt;gt; gt; gt; Yes 1
gt;gt; gt; gt; 1
gt;gt; gt; gt;
gt;gt; gt; gt; I'm looking for the total number of times yes and 1 are in the same
gt;gt; gt; gt; row.
gt;gt; gt; gt; Here it is 2 times.
gt;gt; gt; gt;
gt;gt; gt; gt; Thanks for any help anyone can give me.
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;