SumIF and CountIF formulas

[Guest]

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

 

[Bob Phillips]

=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),"")

--
HTH

Bob

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

 

[Guest]

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),"")

 

[Guest]

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]

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)

 

[Guest]

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
(e-mail address removed) if you would like to respond there.

Thanks