array and countif help!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jcorle - 26 Feb 2008 20:46 GMT

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'!D2:D500="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'!D2:D500="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

Reply to this Message

PCLIVE - 26 Feb 2008 21:02 GMT

Maybe something like this:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1),--(YEAR('2008'!A2:A500)=2008),--('2008'!D2:D500="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
[quoted text clipped - 27 lines]
> City of South Bend
> Engineering

Reply to this Message

jcorle - 27 Feb 2008 13:57 GMT

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

> Maybe something like this:
>
[quoted text clipped - 35 lines]
> > City of South Bend
> > Engineering

Reply to this Message

PCLIVE - 27 Feb 2008 15:28 GMT

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!D2:D500=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!
>
[quoted text clipped - 42 lines]
>> > City of South Bend
>> > Engineering

Reply to this Message

jcorle - 27 Feb 2008 15:42 GMT

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.
>
[quoted text clipped - 62 lines]
> >> > City of South Bend
> >> > Engineering

Reply to this Message

PCLIVE - 27 Feb 2008 17:44 GMT

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'!D2:D500="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'!D2:D500=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'!D2:D500="Quotation
Award"),--('[2008.xls]2008'!E2:E500="Construction"),('[2008.xls]2008'!F2:F500)+(SUM('[2008.xls]2008'!I2:L500)))

HTH,
Paul

> Thanks again...
>
[quoted text clipped - 78 lines]
>> >> > City of South Bend
>> >> > Engineering

Reply to this Message

jcorle - 28 Feb 2008 14:43 GMT

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'!D2:D500="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'!D2:D500="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,
>
[quoted text clipped - 100 lines]
> >> >> > City of South Bend
> >> >> > Engineering

Reply to this Message

PCLIVE - 28 Feb 2008 15:39 GMT

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!D2:D500="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!D2:D500="Claims"),([2008.xls]Sheet1!F2:F500)+([2008.xls]Sheet1!J2:J500))

HTH,
Paul

> Paul,
>
[quoted text clipped - 138 lines]
>> >> >> > City of South Bend
>> >> >> > Engineering

Reply to this Message