SumIF and CountIF formulas
 

I need to:
Sum the total $ of Sheet 1 and Column C if G2,I2,K2,M2, or O2 = 10 and
L2,I2,K2,M2, or O2 = S

also, I need to:
Count the total # of Sheet 1 and Column D if G2,I2,K2,M2, or O2 = 10 and
G2,I2,K2,M2 or O2 = S

Please help!
Todd

SumIF and CountIF formulas
 

=IF(OR(OR(G2=10,I2=10,K2=10,M2=10,O2=10),OR(I2="S" ,K2="S",L2="S",M2="S",2="S")),SUM(C:C),"")

=IF(OR(OR(G2=10,I2=10,K2=10,M2=10,O2=10),OR(I2="S" ,K2="S",L2="S",M2="S",2="S")),COUNT(D:D),"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Todd" wrote in message
...
I need to:
Sum the total $ of Sheet 1 and Column C if G2,I2,K2,M2, or O2 = 10 and
L2,I2,K2,M2, or O2 = S

also, I need to:
Count the total # of Sheet 1 and Column D if G2,I2,K2,M2, or O2 = 10 and
G2,I2,K2,M2 or O2 = S

Please help!
Todd

SumIF and CountIF formulas
 

If you always mean only cells G2,I2,K2,M2,O2 then:

=IF(OR(G2={10,"S"},I2={10,"S"},K2={10,"S"},M2={10, "S"},O2={10,"S"}),SUM(C:C),"")

If the first requirement needs to check L2 then use Bob's formula.


=IF(OR(G2={10,"S"},I2={10,"S"},K2={10,"S"},M2={10, "S"},O2={10,"S"}),SUM(D:D),"")



"Todd" wrote:

I need to:
Sum the total $ of Sheet 1 and Column C if G2,I2,K2,M2, or O2 = 10 and
L2,I2,K2,M2, or O2 = S

also, I need to:
Count the total # of Sheet 1 and Column D if G2,I2,K2,M2, or O2 = 10 and
G2,I2,K2,M2 or O2 = S

Please help!
Todd

SumIF and CountIF formulas
 

Thank you both for your help. This has worked for the most part. I am
making a sales lead tracking program for a construction co. Sheet 1 is the
year (07) and sheet 2 is the General Contractors (GC's). I want to know to
what contractor # have I bid to. Column C is $ Bid to that GC and D is # of
bids to that GC. After that I am looking for how much work I have sold to
that GC, how much I have pending, how much is lost, by both $ and #. Each
lead # can have upto 6 GC's I am bidding to. Bob, when I entered your
formula it worked if I only have data on one row, however when I entered data
on row 2 that did not have the same GC number, it still sumed the bid amount.
The Lead sheet is as follows:
A D J G H I J
K L M N
lead# Job Name Bid amount $ gc1 status gc2 status gc3 status gc4
status
70001 " " $300,000 10 S 14 L 20
L 28 L

So if G2:G150=10,M2:M150=10 etc, SUM C2:C150. This totals the $ bid down
the board. I hope I have not confused you.
Thank You!


"Bob Phillips" wrote:

=IF(OR(OR(G2=10,I2=10,K2=10,M2=10,O2=10),OR(I2="S" ,K2="S",L2="S",M2="S",2="S")),SUM(C:C),"")

=IF(OR(OR(G2=10,I2=10,K2=10,M2=10,O2=10),OR(I2="S" ,K2="S",L2="S",M2="S",2="S")),COUNT(D:D),"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Todd" wrote in message
...
I need to:
Sum the total $ of Sheet 1 and Column C if G2,I2,K2,M2, or O2 = 10 and
L2,I2,K2,M2, or O2 = S

also, I need to:
Count the total # of Sheet 1 and Column D if G2,I2,K2,M2, or O2 = 10 and
G2,I2,K2,M2 or O2 = S

Please help!
Todd

SumIF and CountIF formulas
 

I think you want to sum them, but I am struggling a bit to see where lead is
checked, but try this

=SUMPRODUCT(((G2:G20={10,"S"})+(I2:I20={10,"S"})+( K2:K20={10,"S"})+(M2:M20={10,"S"})+(O2:O20={10,"S" }))*(A2:A20=70001)*(C2:C20))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Todd" wrote in message
...
Thank you both for your help. This has worked for the most part. I am
making a sales lead tracking program for a construction co. Sheet 1 is
the
year (07) and sheet 2 is the General Contractors (GC's). I want to know
to
what contractor # have I bid to. Column C is $ Bid to that GC and D is #
of
bids to that GC. After that I am looking for how much work I have sold to
that GC, how much I have pending, how much is lost, by both $ and #. Each
lead # can have upto 6 GC's I am bidding to. Bob, when I entered your
formula it worked if I only have data on one row, however when I entered
data
on row 2 that did not have the same GC number, it still sumed the bid
amount.
The Lead sheet is as follows:
A D J G H I J
K L M N
lead# Job Name Bid amount $ gc1 status gc2 status gc3 status gc4
status
70001 " " $300,000 10 S 14 L 20
L 28 L

So if G2:G150=10,M2:M150=10 etc, SUM C2:C150. This totals the $ bid down
the board. I hope I have not confused you.
Thank You!


"Bob Phillips" wrote:

=IF(OR(OR(G2=10,I2=10,K2=10,M2=10,O2=10),OR(I2="S" ,K2="S",L2="S",M2="S",2="S")),SUM(C:C),"")

=IF(OR(OR(G2=10,I2=10,K2=10,M2=10,O2=10),OR(I2="S" ,K2="S",L2="S",M2="S",2="S")),COUNT(D:D),"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Todd" wrote in message
...
I need to:
Sum the total $ of Sheet 1 and Column C if G2,I2,K2,M2, or O2 = 10 and
L2,I2,K2,M2, or O2 = S

also, I need to:
Count the total # of Sheet 1 and Column D if G2,I2,K2,M2, or O2 = 10
and
G2,I2,K2,M2 or O2 = S

Please help!
Todd

SumIF and CountIF formulas
 

Bob,
I would like to send the file to you if you are okay with that. If you are
not, then I can provide a very detailed description of the issue but I figure
if you can see it you will be able to understand better. My home e-mail is
if you would like to respond there.

Thanks


"Bob Phillips" wrote:

I think you want to sum them, but I am struggling a bit to see where lead is
checked, but try this

=SUMPRODUCT(((G2:G20={10,"S"})+(I2:I20={10,"S"})+( K2:K20={10,"S"})+(M2:M20={10,"S"})+(O2:O20={10,"S" }))*(A2:A20=70001)*(C2:C20))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Todd" wrote in message
...
Thank you both for your help. This has worked for the most part. I am
making a sales lead tracking program for a construction co. Sheet 1 is
the
year (07) and sheet 2 is the General Contractors (GC's). I want to know
to
what contractor # have I bid to. Column C is $ Bid to that GC and D is #
of
bids to that GC. After that I am looking for how much work I have sold to
that GC, how much I have pending, how much is lost, by both $ and #. Each
lead # can have upto 6 GC's I am bidding to. Bob, when I entered your
formula it worked if I only have data on one row, however when I entered
data
on row 2 that did not have the same GC number, it still sumed the bid
amount.
The Lead sheet is as follows:
A D J G H I J
K L M N
lead# Job Name Bid amount $ gc1 status gc2 status gc3 status gc4
status
70001 " " $300,000 10 S 14 L 20
L 28 L

So if G2:G150=10,M2:M150=10 etc, SUM C2:C150. This totals the $ bid down
the board. I hope I have not confused you.
Thank You!


"Bob Phillips" wrote:

=IF(OR(OR(G2=10,I2=10,K2=10,M2=10,O2=10),OR(I2="S" ,K2="S",L2="S",M2="S",2="S")),SUM(C:C),"")

=IF(OR(OR(G2=10,I2=10,K2=10,M2=10,O2=10),OR(I2="S" ,K2="S",L2="S",M2="S",2="S")),COUNT(D:D),"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Todd" wrote in message
...
I need to:
Sum the total $ of Sheet 1 and Column C if G2,I2,K2,M2, or O2 = 10 and
L2,I2,K2,M2, or O2 = S

also, I need to:
Count the total # of Sheet 1 and Column D if G2,I2,K2,M2, or O2 = 10
and
G2,I2,K2,M2 or O2 = S

Please help!
Todd