multiple text criteria sum if function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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.
 
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
 
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
 
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
 
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...
 
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...
 
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)
 
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...
 
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!
 
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...
 
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...
 
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...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sum product with criteria from 2 wkshts 3
sumproduct using criteria from multiple columns in a separate wksh 1
sumproduct using criteria from 2 worksheets 1
SUMIF with criteria "<>" & "=" 4
Formula Question 3
Formula Question 1
calculating a mean using sumproduct 8
Formulas with percentages 3

Back
Top