array and countif help!

[jcorle]

I have tried and tried to get this to work. I am trying to do this all in one
formula and I think it's outsmarted me... maybe it outsmarted me from the
beginning, but I always thought I was pretty good at this.

Anyway, I am tring take the following information from one worksheet and put
it into another... Ultimately, I want to get How many Quoation Awards were
made for Construction in the month of January...
Column A: Date approved
Column D: Type (Quoation Award)
Column E: Code (Type of Quotation made)
Column F: Number (1 if quote, 0 if not)

=COUNT(IF('2008'!A2:A500,">=1/1/2008")-COUNTIF('2008'!A2:A500,">1/31/2008")-('[2008]2008'!D2500="Quotation
Award")*('[2008]2008'!E2:E500="Construction"),('[2008]2008'!F2:F500))

I also tried it this way, but this doesn't include the January bit.:
=COUNT(IF(('[2008]2008'!D2500="Quotation
Award")*('[2008]2008'!E2:E500="Construction"),('[2008]2008'!F2:F500)))

Also, everytime I update a cell, it wants me to update the data from the
spreadsheet... HUH?! I'm ready to scrap the whole thing and start over.

Any help would be GREATLY appreciated - I also am going to do it for several
other items, but once I get it, I can change it to fit!

Jennifer L. Corle
City of South Bend
Engineering

 

[PCLIVE]

Maybe something like this:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1),--(YEAR('2008'!A2:A500)=2008),--('2008'!D2500="Quotation
Award"),--('2008'!E2:E500="Construction"),('2008'!F2:F500))

HTH,
Paul

 

[jcorle]

THANK YOU THANK YOU! You are WONDERFUL! That formula is perfect!

Maybe something like this:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1),--(YEAR('2008'!A2:A500)=2008),--('2008'!D2500="Quotation
Award"),--('2008'!E2:E500="Construction"),('2008'!F2:F500))

HTH,
Paul

--

I have tried and tried to get this to work. I am trying to do this all in
one
formula and I think it's outsmarted me... maybe it outsmarted me from the
beginning, but I always thought I was pretty good at this.

Anyway, I am tring take the following information from one worksheet and
put
it into another... Ultimately, I want to get How many Quoation Awards were
made for Construction in the month of January...
Column A: Date approved
Column D: Type (Quoation Award)
Column E: Code (Type of Quotation made)
Column F: Number (1 if quote, 0 if not)

=COUNT(IF('2008'!A2:A500,">=1/1/2008")-COUNTIF('2008'!A2:A500,">1/31/2008")-('[2008]2008'!D2500="Quotation
Award")*('[2008]2008'!E2:E500="Construction"),('[2008]2008'!F2:F500))

I also tried it this way, but this doesn't include the January bit.:
=COUNT(IF(('[2008]2008'!D2500="Quotation
Award")*('[2008]2008'!E2:E500="Construction"),('[2008]2008'!F2:F500)))

Also, everytime I update a cell, it wants me to update the data from the
spreadsheet... HUH?! I'm ready to scrap the whole thing and start over.

Any help would be GREATLY appreciated - I also am going to do it for
several
other items, but once I get it, I can change it to fit!

Jennifer L. Corle
City of South Bend
Engineering

 

[PCLIVE]

You're welcome.

You might also consider having the date you want to match the month and year
to in a cell. Example - Let's say you put a date in cell A1 of the current
sheet. That date should include the month and year that you want to pull
data for. (1/15/2008 the day (15) is not important as long as it is valid.
Now you can change your formula to reference that cell when matching the
month and year. This will allow you to change just the date in that cell.

Additionally, let's say the other criteria are also predefined in cells.
Date = A1
"Quotiation Award" = A2
"Construction" = A3

So now the formula might be:
=SUMPRODUCT(--(MONTH([2008.xls]Sheet1!A2:A500)=MONTH(A1)),--(YEAR([2008.xls]Sheet1!A2:A500)=YEAR(A1)),--([2008.xls]Sheet1!D2500=A2),--([2008.xls]Sheet1!E2:E500=A3),([2008.xls]Sheet1!F2:F500))

Regards,
Paul




--

THANK YOU THANK YOU! You are WONDERFUL! That formula is perfect!

Maybe something like this:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1),--(YEAR('2008'!A2:A500)=2008),--('2008'!D2500="Quotation
Award"),--('2008'!E2:E500="Construction"),('2008'!F2:F500))

HTH,
Paul

--

I have tried and tried to get this to work. I am trying to do this all
in
one
formula and I think it's outsmarted me... maybe it outsmarted me from
the
beginning, but I always thought I was pretty good at this.

Anyway, I am tring take the following information from one worksheet
and
put
it into another... Ultimately, I want to get How many Quoation Awards
were
made for Construction in the month of January...
Column A: Date approved
Column D: Type (Quoation Award)
Column E: Code (Type of Quotation made)
Column F: Number (1 if quote, 0 if not)

=COUNT(IF('2008'!A2:A500,">=1/1/2008")-COUNTIF('2008'!A2:A500,">1/31/2008")-('[2008]2008'!D2500="Quotation
Award")*('[2008]2008'!E2:E500="Construction"),('[2008]2008'!F2:F500))

I also tried it this way, but this doesn't include the January bit.:
=COUNT(IF(('[2008]2008'!D2500="Quotation
Award")*('[2008]2008'!E2:E500="Construction"),('[2008]2008'!F2:F500)))

Also, everytime I update a cell, it wants me to update the data from
the
spreadsheet... HUH?! I'm ready to scrap the whole thing and start over.

Any help would be GREATLY appreciated - I also am going to do it for
several
other items, but once I get it, I can change it to fit!

Jennifer L. Corle
City of South Bend
Engineering

 

[jcorle]

Thanks again...

What if I wanted to add the cost amounts from columns I2 to L500 meeting the
same standards as before?

Jen

You're welcome.

You might also consider having the date you want to match the month and year
to in a cell. Example - Let's say you put a date in cell A1 of the current
sheet. That date should include the month and year that you want to pull
data for. (1/15/2008 the day (15) is not important as long as it is valid.
Now you can change your formula to reference that cell when matching the
month and year. This will allow you to change just the date in that cell.

Additionally, let's say the other criteria are also predefined in cells.
Date = A1
"Quotiation Award" = A2
"Construction" = A3

So now the formula might be:
=SUMPRODUCT(--(MONTH([2008.xls]Sheet1!A2:A500)=MONTH(A1)),--(YEAR([2008.xls]Sheet1!A2:A500)=YEAR(A1)),--([2008.xls]Sheet1!D2500=A2),--([2008.xls]Sheet1!E2:E500=A3),([2008.xls]Sheet1!F2:F500))

Regards,
Paul




--

THANK YOU THANK YOU! You are WONDERFUL! That formula is perfect!

Maybe something like this:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1),--(YEAR('2008'!A2:A500)=2008),--('2008'!D2500="Quotation
Award"),--('2008'!E2:E500="Construction"),('2008'!F2:F500))

HTH,
Paul

--

I have tried and tried to get this to work. I am trying to do this all
in
one
formula and I think it's outsmarted me... maybe it outsmarted me from
the
beginning, but I always thought I was pretty good at this.

Anyway, I am tring take the following information from one worksheet
and
put
it into another... Ultimately, I want to get How many Quoation Awards
were
made for Construction in the month of January...
Column A: Date approved
Column D: Type (Quoation Award)
Column E: Code (Type of Quotation made)
Column F: Number (1 if quote, 0 if not)

=COUNT(IF('2008'!A2:A500,">=1/1/2008")-COUNTIF('2008'!A2:A500,">1/31/2008")-('[2008]2008'!D2500="Quotation
Award")*('[2008]2008'!E2:E500="Construction"),('[2008]2008'!F2:F500))

I also tried it this way, but this doesn't include the January bit.:
=COUNT(IF(('[2008]2008'!D2500="Quotation
Award")*('[2008]2008'!E2:E500="Construction"),('[2008]2008'!F2:F500)))

Also, everytime I update a cell, it wants me to update the data from
the
spreadsheet... HUH?! I'm ready to scrap the whole thing and start over.

Any help would be GREATLY appreciated - I also am going to do it for
several
other items, but once I get it, I can change it to fit!

Jennifer L. Corle
City of South Bend
Engineering

 

[PCLIVE]

Ok,

You stated I2 to L500.
Assuming that was supposed to be L2 to L500 and also assuming you mean
instead of column F, then:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1),--(YEAR('2008'!A2:A500)=2008),--('2008'!D2500="Quotation
Award"),--('2008'!E2:E500="Construction"),('2008'!L2:L500))

Now, if you mean L2 to L500 added to F2 to F500, then:

=SUMPRODUCT(--(MONTH('[2008.xls]2008'!A2:A500)=MONTH(A1)),--(YEAR('[2008.xls]2008'!A2:A500)=YEAR(A1)),--('[2008.xls]2008'!D2500=A2),--('[2008.xls]2008'!E2:E500=A3),('[2008.xls]2008'!F2:F500)+('[2008.xls]2008'!I2:I500))


If you DID mean the sume of I2 to L500 and adding to F2 to F500, then maybe
this:

=SUMPRODUCT(--(MONTH('[2008.xls]2008'!A2:A500)=MONTH(A1)),--(YEAR('[2008.xls]2008'!A2:A500)=YEAR(A1)),--('[2008.xls]2008'!D2500="Quotation
Award"),--('[2008.xls]2008'!E2:E500="Construction"),('[2008.xls]2008'!F2:F500)+(SUM('[2008.xls]2008'!I2:L500)))


HTH,
Paul




--

Thanks again...

What if I wanted to add the cost amounts from columns I2 to L500 meeting
the
same standards as before?

Jen

You're welcome.

You might also consider having the date you want to match the month and
year
to in a cell. Example - Let's say you put a date in cell A1 of the
current
sheet. That date should include the month and year that you want to pull
data for. (1/15/2008 the day (15) is not important as long as it is
valid.
Now you can change your formula to reference that cell when matching the
month and year. This will allow you to change just the date in that
cell.

Additionally, let's say the other criteria are also predefined in cells.
Date = A1
"Quotiation Award" = A2
"Construction" = A3

So now the formula might be:
=SUMPRODUCT(--(MONTH([2008.xls]Sheet1!A2:A500)=MONTH(A1)),--(YEAR([2008.xls]Sheet1!A2:A500)=YEAR(A1)),--([2008.xls]Sheet1!D2500=A2),--([2008.xls]Sheet1!E2:E500=A3),([2008.xls]Sheet1!F2:F500))

Regards,
Paul




--

THANK YOU THANK YOU! You are WONDERFUL! That formula is perfect!

:

Maybe something like this:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1),--(YEAR('2008'!A2:A500)=2008),--('2008'!D2500="Quotation
Award"),--('2008'!E2:E500="Construction"),('2008'!F2:F500))

HTH,
Paul

--

I have tried and tried to get this to work. I am trying to do this
all
in
one
formula and I think it's outsmarted me... maybe it outsmarted me
from
the
beginning, but I always thought I was pretty good at this.

Anyway, I am tring take the following information from one worksheet
and
put
it into another... Ultimately, I want to get How many Quoation
Awards
were
made for Construction in the month of January...
Column A: Date approved
Column D: Type (Quoation Award)
Column E: Code (Type of Quotation made)
Column F: Number (1 if quote, 0 if not)

=COUNT(IF('2008'!A2:A500,">=1/1/2008")-COUNTIF('2008'!A2:A500,">1/31/2008")-('[2008]2008'!D2500="Quotation
Award")*('[2008]2008'!E2:E500="Construction"),('[2008]2008'!F2:F500))

I also tried it this way, but this doesn't include the January bit.:
=COUNT(IF(('[2008]2008'!D2500="Quotation
Award")*('[2008]2008'!E2:E500="Construction"),('[2008]2008'!F2:F500)))

Also, everytime I update a cell, it wants me to update the data from
the
spreadsheet... HUH?! I'm ready to scrap the whole thing and start
over.

Any help would be GREATLY appreciated - I also am going to do it for
several
other items, but once I get it, I can change it to fit!

Jennifer L. Corle
City of South Bend
Engineering

 

[jcorle]

Paul,

I'm so sorry to push this. I have 33 other 'types' of instances to count
besides Quotation Openings so I can't use the short cut. I guess I don't
need the year part because everything in this workbook is going to be 2008.
I was able to modify the second calculation in your last response to suit
what I needed:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1))--('2008'!D2500="Quotation
Award")--('2008'!E2:E500="Construction")*('2008'!F2:F500)+(SUM('2008'!J2:J500))

however, it is calcluating everything in the J column not just those records
that would fit in the Quotation award and Construction categories. Also, it
keeps automatically taking out the commas between the sections.

Another example is I changed the query standards to just the month = january
and the typic as claims and it comes up with the same amount as above.

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1))--('2008'!D2500="Claims")*('2008'!F2:F500)+(SUM('2008'!J2:J500))

Like I said, I'm really sorry to push this. I do appreciate your help.

Jen

Ok,

You stated I2 to L500.
Assuming that was supposed to be L2 to L500 and also assuming you mean
instead of column F, then:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1),--(YEAR('2008'!A2:A500)=2008),--('2008'!D2500="Quotation
Award"),--('2008'!E2:E500="Construction"),('2008'!L2:L500))

Now, if you mean L2 to L500 added to F2 to F500, then:

=SUMPRODUCT(--(MONTH('[2008.xls]2008'!A2:A500)=MONTH(A1)),--(YEAR('[2008.xls]2008'!A2:A500)=YEAR(A1)),--('[2008.xls]2008'!D2500=A2),--('[2008.xls]2008'!E2:E500=A3),('[2008.xls]2008'!F2:F500)+('[2008.xls]2008'!I2:I500))


If you DID mean the sume of I2 to L500 and adding to F2 to F500, then maybe
this:

=SUMPRODUCT(--(MONTH('[2008.xls]2008'!A2:A500)=MONTH(A1)),--(YEAR('[2008.xls]2008'!A2:A500)=YEAR(A1)),--('[2008.xls]2008'!D2500="Quotation
Award"),--('[2008.xls]2008'!E2:E500="Construction"),('[2008.xls]2008'!F2:F500)+(SUM('[2008.xls]2008'!I2:L500)))


HTH,
Paul




--

Thanks again...

What if I wanted to add the cost amounts from columns I2 to L500 meeting
the
same standards as before?

Jen

You're welcome.

You might also consider having the date you want to match the month and
year
to in a cell. Example - Let's say you put a date in cell A1 of the
current
sheet. That date should include the month and year that you want to pull
data for. (1/15/2008 the day (15) is not important as long as it is
valid.
Now you can change your formula to reference that cell when matching the
month and year. This will allow you to change just the date in that
cell.

Additionally, let's say the other criteria are also predefined in cells.
Date = A1
"Quotiation Award" = A2
"Construction" = A3

So now the formula might be:
=SUMPRODUCT(--(MONTH([2008.xls]Sheet1!A2:A500)=MONTH(A1)),--(YEAR([2008.xls]Sheet1!A2:A500)=YEAR(A1)),--([2008.xls]Sheet1!D2500=A2),--([2008.xls]Sheet1!E2:E500=A3),([2008.xls]Sheet1!F2:F500))

Regards,
Paul




--

THANK YOU THANK YOU! You are WONDERFUL! That formula is perfect!

:

Maybe something like this:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1),--(YEAR('2008'!A2:A500)=2008),--('2008'!D2500="Quotation
Award"),--('2008'!E2:E500="Construction"),('2008'!F2:F500))

HTH,
Paul

--

I have tried and tried to get this to work. I am trying to do this
all
in
one
formula and I think it's outsmarted me... maybe it outsmarted me
from
the
beginning, but I always thought I was pretty good at this.

Anyway, I am tring take the following information from one worksheet
and
put
it into another... Ultimately, I want to get How many Quoation
Awards
were
made for Construction in the month of January...
Column A: Date approved
Column D: Type (Quoation Award)
Column E: Code (Type of Quotation made)
Column F: Number (1 if quote, 0 if not)

=COUNT(IF('2008'!A2:A500,">=1/1/2008")-COUNTIF('2008'!A2:A500,">1/31/2008")-('[2008]2008'!D2500="Quotation
Award")*('[2008]2008'!E2:E500="Construction"),('[2008]2008'!F2:F500))

I also tried it this way, but this doesn't include the January bit.:
=COUNT(IF(('[2008]2008'!D2500="Quotation
Award")*('[2008]2008'!E2:E500="Construction"),('[2008]2008'!F2:F500)))

Also, everytime I update a cell, it wants me to update the data from
the
spreadsheet... HUH?! I'm ready to scrap the whole thing and start
over.

Any help would be GREATLY appreciated - I also am going to do it for
several
other items, but once I get it, I can change it to fit!

Jennifer L. Corle
City of South Bend
Engineering

 

[PCLIVE]

Hi Jen,

As you pointed out, there were some missing some commas. There was also an
added asterisk. Additionally, the SUM function is not needed nor will it
function the way I had orginally suggested. Your first formula that you
modified should be this:

=SUMPRODUCT(--(MONTH([2008.xls]Sheet1!A2:A500)=1),--([2008.xls]Sheet1!D2500="Quotation
Award"),--([2008.xls]Sheet1!E2:E500="Construction"),([2008.xls]Sheet1!F2:F500)+([2008.xls]Sheet1!J2:J500))

Your second formula should be this:

=SUMPRODUCT(--(MONTH([2008.xls]Sheet1!A2:A500)=1),--([2008.xls]Sheet1!D2500="Claims"),([2008.xls]Sheet1!F2:F500)+([2008.xls]Sheet1!J2:J500))

HTH,
Paul



--

Paul,

I'm so sorry to push this. I have 33 other 'types' of instances to count
besides Quotation Openings so I can't use the short cut. I guess I don't
need the year part because everything in this workbook is going to be
2008.
I was able to modify the second calculation in your last response to suit
what I needed:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1))--('2008'!D2500="Quotation
Award")--('2008'!E2:E500="Construction")*('2008'!F2:F500)+(SUM('2008'!J2:J500))

however, it is calcluating everything in the J column not just those
records
that would fit in the Quotation award and Construction categories. Also,
it
keeps automatically taking out the commas between the sections.

Another example is I changed the query standards to just the month =
january
and the typic as claims and it comes up with the same amount as above.

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1))--('2008'!D2500="Claims")*('2008'!F2:F500)+(SUM('2008'!J2:J500))

Like I said, I'm really sorry to push this. I do appreciate your help.

Jen

Ok,

You stated I2 to L500.
Assuming that was supposed to be L2 to L500 and also assuming you mean
instead of column F, then:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1),--(YEAR('2008'!A2:A500)=2008),--('2008'!D2500="Quotation
Award"),--('2008'!E2:E500="Construction"),('2008'!L2:L500))

Now, if you mean L2 to L500 added to F2 to F500, then:

=SUMPRODUCT(--(MONTH('[2008.xls]2008'!A2:A500)=MONTH(A1)),--(YEAR('[2008.xls]2008'!A2:A500)=YEAR(A1)),--('[2008.xls]2008'!D2500=A2),--('[2008.xls]2008'!E2:E500=A3),('[2008.xls]2008'!F2:F500)+('[2008.xls]2008'!I2:I500))


If you DID mean the sume of I2 to L500 and adding to F2 to F500, then
maybe
this:

=SUMPRODUCT(--(MONTH('[2008.xls]2008'!A2:A500)=MONTH(A1)),--(YEAR('[2008.xls]2008'!A2:A500)=YEAR(A1)),--('[2008.xls]2008'!D2500="Quotation
Award"),--('[2008.xls]2008'!E2:E500="Construction"),('[2008.xls]2008'!F2:F500)+(SUM('[2008.xls]2008'!I2:L500)))


HTH,
Paul




--

Thanks again...

What if I wanted to add the cost amounts from columns I2 to L500
meeting
the
same standards as before?

Jen

:

You're welcome.

You might also consider having the date you want to match the month
and
year
to in a cell. Example - Let's say you put a date in cell A1 of the
current
sheet. That date should include the month and year that you want to
pull
data for. (1/15/2008 the day (15) is not important as long as it is
valid.
Now you can change your formula to reference that cell when matching
the
month and year. This will allow you to change just the date in that
cell.

Additionally, let's say the other criteria are also predefined in
cells.
Date = A1
"Quotiation Award" = A2
"Construction" = A3

So now the formula might be:
=SUMPRODUCT(--(MONTH([2008.xls]Sheet1!A2:A500)=MONTH(A1)),--(YEAR([2008.xls]Sheet1!A2:A500)=YEAR(A1)),--([2008.xls]Sheet1!D2500=A2),--([2008.xls]Sheet1!E2:E500=A3),([2008.xls]Sheet1!F2:F500))

Regards,
Paul




--

THANK YOU THANK YOU! You are WONDERFUL! That formula is perfect!

:

Maybe something like this:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1),--(YEAR('2008'!A2:A500)=2008),--('2008'!D2500="Quotation
Award"),--('2008'!E2:E500="Construction"),('2008'!F2:F500))

HTH,
Paul

--

I have tried and tried to get this to work. I am trying to do this
all
in
one
formula and I think it's outsmarted me... maybe it outsmarted me
from
the
beginning, but I always thought I was pretty good at this.

Anyway, I am tring take the following information from one
worksheet
and
put
it into another... Ultimately, I want to get How many Quoation
Awards
were
made for Construction in the month of January...
Column A: Date approved
Column D: Type (Quoation Award)
Column E: Code (Type of Quotation made)
Column F: Number (1 if quote, 0 if not)

=COUNT(IF('2008'!A2:A500,">=1/1/2008")-COUNTIF('2008'!A2:A500,">1/31/2008")-('[2008]2008'!D2500="Quotation
Award")*('[2008]2008'!E2:E500="Construction"),('[2008]2008'!F2:F500))

I also tried it this way, but this doesn't include the January
bit.:
=COUNT(IF(('[2008]2008'!D2500="Quotation
Award")*('[2008]2008'!E2:E500="Construction"),('[2008]2008'!F2:F500)))

Also, everytime I update a cell, it wants me to update the data
from
the
spreadsheet... HUH?! I'm ready to scrap the whole thing and start
over.

Any help would be GREATLY appreciated - I also am going to do it
for
several
other items, but once I get it, I can change it to fit!

Jennifer L. Corle
City of South Bend
Engineering