multiple text criteria sum if function

D

datasorter

can anyone tell me whats wrong with this formula and how to fix it?

=sumif(and('[Upsell Monthly Report.xls]COB'!$A:$A,'[Upsell Monthly
Report.xls]COB'!$C:$C),(A8,B8),('[Upsell Monthly Report.xls]COB'!$I:$I)
 
B

Barb Reinhardt

What do you want to do with this? Is is possibly a SUMPRODUCT formula?
Since I'm having some SUMPRODUCT difficulties today, I'll leave this to
someone else.
 
V

VBA Noob

Thinks it's your criteria A8,B8 as it's not a range and A8 is in Colum
A which is a circular ref.

VBA Noo
 
P

Peo Sjoblom

1. SUMIF does not work if the file is closed (same goes for COUNTIF)

2. Your criteria is a bit unclear, do you mean either A8 or B8

3. You can use SUMPRODUCT but you cannot use the whole column like A:A, you
need a specified range,
here are 2 formulas that should work, I was too lazy to use your path but
the formulas work with closed workbooks



=SUMPRODUCT(--((COB!A2:A5000=A8)+(COB!A2:A5000=B8)>0),COB!I2:I5000)

or

=SUMPRODUCT((COB!A2:A5000=A8:B8)*(COB!I2:I5000))

the former is more stable since it won't choke if there is an occasional
text value in the range



--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com




I will leave out the path and workbook name, you can add that yourself but
 
T

Toppers

Try:

=SUMPRODUCT(--('[Upsell Monthly Report.xls]COB'!$A1:$A1000=A8),--('[Upsell
Monthly Report.xls]COB'!$C1:$C1000=B8)*('[Upsell Monthly
Report.xls]COB'!$I1:$I000))

SUMPRODUCT must have defined arrays i.e. cannot use whole columns, so adjust
to you needs. All ranges must be same size.

HTH
 
D

datasorter

Hi,

Im trying to use this function to grab a cell in a different workbook.

=sumif(and('[Upsell Monthly Report.xls]COB'!$A:$A,'[Upsell Monthly
Report.xls]COB'!$C:$C),(A8,B8),('[Upsell Monthly Report.xls]COB'!$I:$I)

I added in the 'and' because I thought it would enable me to have two
ranges. (Column A and Column C). The A8 and B8 are the criteria that I need
to match within Column A and C in the other workbook.

Finally, column I is the colmun on the other sheet that I am pulling the
number from.

I'm thinking of a possible alternative in place on the end instead of the
sum range in Column I.


Any help is much appreciated.


Barb Reinhardt said:
What do you want to do with this? Is is possibly a SUMPRODUCT formula?
Since I'm having some SUMPRODUCT difficulties today, I'll leave this to
someone else.

datasorter said:
can anyone tell me whats wrong with this formula and how to fix it?

=sumif(and('[Upsell Monthly Report.xls]COB'!$A:$A,'[Upsell Monthly
Report.xls]COB'!$C:$C),(A8,B8),('[Upsell Monthly Report.xls]COB'!$I:$I)
Click to expand...
Click to expand...
 
D

datasorter

I changed your '$I1:$I000' to '$I1:$I1000' and I got #REF!

Toppers said:
Try:

=SUMPRODUCT(--('[Upsell Monthly Report.xls]COB'!$A1:$A1000=A8),--('[Upsell
Monthly Report.xls]COB'!$C1:$C1000=B8)*('[Upsell Monthly
Report.xls]COB'!$I1:$I000))

SUMPRODUCT must have defined arrays i.e. cannot use whole columns, so adjust
to you needs. All ranges must be same size.

HTH

datasorter said:
can anyone tell me whats wrong with this formula and how to fix it?

=sumif(and('[Upsell Monthly Report.xls]COB'!$A:$A,'[Upsell Monthly
Report.xls]COB'!$C:$C),(A8,B8),('[Upsell Monthly Report.xls]COB'!$I:$I)
Click to expand...
Click to expand...
 
D

datasorter

I tried this and I got #N/A

=SUMPRODUCT(--(('[Upsell Monthly Report.xls]COB'!$A$19:$A$944=A8)+('[Upsell
Monthly Report.xls]COB'!$C$19:$C$891=C8)>0),'[Upsell Monthly
Report.xls]COB'!$I$19:$I$1075)
 
T

Toppers

Check all references e.g Workbook names are correct. I tried using your
w/book name and worked fine.

datasorter said:
I changed your '$I1:$I000' to '$I1:$I1000' and I got #REF!

Toppers said:
Try:

=SUMPRODUCT(--('[Upsell Monthly Report.xls]COB'!$A1:$A1000=A8),--('[Upsell
Monthly Report.xls]COB'!$C1:$C1000=B8)*('[Upsell Monthly
Report.xls]COB'!$I1:$I000))

SUMPRODUCT must have defined arrays i.e. cannot use whole columns, so adjust
to you needs. All ranges must be same size.

HTH

datasorter said:
can anyone tell me whats wrong with this formula and how to fix it?

=sumif(and('[Upsell Monthly Report.xls]COB'!$A:$A,'[Upsell Monthly
Report.xls]COB'!$C:$C),(A8,B8),('[Upsell Monthly Report.xls]COB'!$I:$I)
Click to expand...
Click to expand...
Click to expand...
 
R

Ragdyer

Your ranges are *not equal*!

They *must all* be exactly the same size.

A19:A944
C19:C891
I19:I1075

Pick whatever range best suits your data, and equalize them ... THEN ... try
again!
 
P

Peo Sjoblom

True but my formula was wrong since I didn't observe that the OP used the A,
the C and the I range, so it would be a regular sumproduct not the OR
version I cooked up so

=SUMPRODUCT(--('[Upsell Monthly
Report.xls]COB'!$A$19:$A$1075=A8),--('[Upsell
Monthly Report.xls]COB'!$C$19:$C$1075=C8),'[Upsell Monthly
Report.xls]COB'!$I$19:$I$1075)

might work


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com




Ragdyer said:
Your ranges are *not equal*!

They *must all* be exactly the same size.

A19:A944
C19:C891
I19:I1075

Pick whatever range best suits your data, and equalize them ... THEN ...
try again!

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
datasorter said:
I tried this and I got #N/A

=SUMPRODUCT(--(('[Upsell Monthly
Report.xls]COB'!$A$19:$A$944=A8)+('[Upsell
Monthly Report.xls]COB'!$C$19:$C$891=C8)>0),'[Upsell Monthly
Report.xls]COB'!$I$19:$I$1075)
Click to expand...
Click to expand...
 
R

RagDyeR

It's *still* not clear (to me) exactly what criteria refers to what range.

The way the OP formula was constructed, anything is (was) possible.<g>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

True but my formula was wrong since I didn't observe that the OP used the A,
the C and the I range, so it would be a regular sumproduct not the OR
version I cooked up so

=SUMPRODUCT(--('[Upsell Monthly
Report.xls]COB'!$A$19:$A$1075=A8),--('[Upsell
Monthly Report.xls]COB'!$C$19:$C$1075=C8),'[Upsell Monthly
Report.xls]COB'!$I$19:$I$1075)

might work


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com




Ragdyer said:
Your ranges are *not equal*!

They *must all* be exactly the same size.

A19:A944
C19:C891
I19:I1075

Pick whatever range best suits your data, and equalize them ... THEN ...
try again!

--
HTH,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -
datasorter said:
I tried this and I got #N/A

=SUMPRODUCT(--(('[Upsell Monthly
Report.xls]COB'!$A$19:$A$944=A8)+('[Upsell
Monthly Report.xls]COB'!$C$19:$C$891=C8)>0),'[Upsell Monthly
Report.xls]COB'!$I$19:$I$1075)
Click to expand...
Click to expand...
 
P

Peo Sjoblom

Bingo <bg>

Peo


RagDyeR said:
It's *still* not clear (to me) exactly what criteria refers to what range.

The way the OP formula was constructed, anything is (was) possible.<g>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

True but my formula was wrong since I didn't observe that the OP used the
A,
the C and the I range, so it would be a regular sumproduct not the OR
version I cooked up so

=SUMPRODUCT(--('[Upsell Monthly
Report.xls]COB'!$A$19:$A$1075=A8),--('[Upsell
Monthly Report.xls]COB'!$C$19:$C$1075=C8),'[Upsell Monthly
Report.xls]COB'!$I$19:$I$1075)

might work


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com




Ragdyer said:
Your ranges are *not equal*!

They *must all* be exactly the same size.

A19:A944
C19:C891
I19:I1075

Pick whatever range best suits your data, and equalize them ... THEN ...
try again!

--
HTH,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -
datasorter said:
I tried this and I got #N/A

=SUMPRODUCT(--(('[Upsell Monthly
Report.xls]COB'!$A$19:$A$944=A8)+('[Upsell
Monthly Report.xls]COB'!$C$19:$C$891=C8)>0),'[Upsell Monthly
Report.xls]COB'!$I$19:$I$1075)
Click to expand...
Click to expand...
Click to expand...
 
Top