How do I use SUMPRODUCT to checks a range for multiple criteria?

[stuu3270]

Hi,

I recently posted about using SUMPRODUCT to check for a month, as well as two other criteria in two other ranges. The post is here - http://www.mrexcel.com/forum/excel-questions/691766-using-sumproduct-adding-month-criteria.html

However, using the same formula in Excel 2003:

=SUMPRODUCT(--(Correspondence!$B$36:$B$999=admin!$B$15),--(Correspondence!$C$36:$C$999=Correspondence!A8),--(MONTH(Correspondence!A36:A999)=2))

I have been trying to figure out how I can do the following - I want to check down column C:C as above but to see if it matches Correspondence!A8:A15, or maybe A28:A35 for another formula.

Again I have found some pointers elsewhere and tried to them, tweak them, adapt them but so far I cannot get it to work.

So, to clarify, I want the above formula to still check Correspondence!B36:B99 for ADMIN!B15,
I still want it to check the month in Correspondence!A36:A999 but instead of matching just Correspondence!A8, I want it to check for Correspondence!A11, or A12 or A13 or A14 or A15 and populate the cell with the total of all of these 5 things for a certain month by a certain person.

Any ideas?

Stu

 

[Aladin Akyurek]

Hi,

I recently posted about using SUMPRODUCT to check for a month, as well as two other criteria in two other ranges. The post is here - http://www.mrexcel.com/forum/excel-questions/691766-using-sumproduct-adding-month-criteria.html

However, using the same formula in Excel 2003:

=SUMPRODUCT(--(Correspondence!$B$36:$B$999=admin!$B$15),--(Correspondence!$C$36:$C$999=Correspondence!A8),--(MONTH(Correspondence!A36:A999)=2))

I have been trying to figure out how I can do the following - I want to check down column C:C as above but to see if it matches Correspondence!A8:A15, or maybe A28:A35 for another formula.

Again I have found some pointers elsewhere and tried to them, tweak them, adapt them but so far I cannot get it to work.

So, to clarify, I want the above formula to still check Correspondence!B36:B99 for ADMIN!B15,
I still want it to check the month in Correspondence!A36:A999 but instead of matching just Correspondence!A8, I want it to check for Correspondence!A11, or A12 or A13 or A14 or A15 and populate the cell with the total of all of these 5 things for a certain month by a certain person.

Any ideas?

Stu

On which sheet and which cell are you invoking this formula? And the month of which year are we looking for?

 

[stuu3270]

I have a sheet 'Correspondence' that shows a table of what correspondence people have submitted, on which date and what their name and ID are. I have an 'ADMIN' sheet with various values entered into a column that adds the data to corresponding userform buttons and the correspondence worksheet (i.e. when somebody adds a person's ID and name all sheets or userforms are updated accordingly. I have a sheet for each month which shows me a monthly summary of the correspondence for each month.

I put the formulae into various celss in the month sheet (i.e. January, February, March...) that uses ranges in Correspondence and ADMIN to check for the criteria.
Exactly as the existing formula shows but I want to search a range or column for more than one criteria in one formula, instead of using separate cells and formulae for each criteria.

I suppose I want something like: =SUMPRODUCT(--(Correspondence!$B$36:$B$999=admin!$B$15),--(Correspondence!$C$36:$C$999=Correspondence!A8 or A9 or A10 or A11),--(MONTH(Correspondence!A36:A999)=2))

where the bold part is to look for items that match more than one thing in that range.

Make sense?
Stu

 

[Aladin Akyurek]

I have a sheet 'Correspondence' that shows a table of what correspondence people have submitted, on which date and what their name and ID are. I have an 'ADMIN' sheet with various values entered into a column that adds the data to corresponding userform buttons and the correspondence worksheet (i.e. when somebody adds a person's ID and name all sheets or userforms are updated accordingly. I have a sheet for each month which shows me a monthly summary of the correspondence for each month.

I put the formulae into various celss in the month sheet (i.e. January, February, March...) that uses ranges in Correspondence and ADMIN to check for the criteria.
Exactly as the existing formula shows but I want to search a range or column for more than one criteria in one formula, instead of using separate cells and formulae for each criteria.

I suppose I want something like: =SUMPRODUCT(--(Correspondence!$B$36:$B$999=admin!$B$15),--(Correspondence!$C$36:$C$999=Correspondence!A8 or A9 or A10 or A11),--(MONTH(Correspondence!A36:A999)=2))

where the bold part is to look for items that match more than one thing in that range.

Make sense?
Stu

I did not get exactly what I wanted to know...

It's not a good idea to reference Correspondence!A8 or A9 or A10 or A11 as criteria to evaluate. The values they house should have cells their own.

Let C15:F15 on admin house those criterion values that must hold for Correspondence!$C$36:$C$999.

Now try...

=SUMPRODUCT(
  --(Correspondence!$B$36:$B$999=admin!$B$15),
  --ISNUMBER(MATCH(Correspondence!$C$36:$C$999,admin!C15:F15)),
  --(MONTH(Correspondence!A36:A999)=2))

 

[stuu3270]

Thanks,

Actually, ADMIN does already house the values I want to match at A35:A39 (or similar). So can I change your formula to --ISNUMBER(MATCH(Correspondence!$C$36:$C$999,admin!A35:A39)) ?

Stu

 

[Aladin Akyurek]

Thanks,

Actually, ADMIN does already house the values I want to match at A35:A39 (or similar). So can I change your formula to --ISNUMBER(MATCH(Correspondence!$C$36:$C$999,admin!A35:A39)) ?

Absolutely.

By the way, what dofference does it make using the $ symbol within a formula?
Stu

In $A1 is the column part of the reference is locked; in A$1 the row part, and $A$1 both part. The part that is locked won't change when copied down or across as the case may be.

 

[stuu3270]

Hello again,The criteria search doesn't seem to be working. I have put in my range where B31:B35 is what I want to count - =SUMPRODUCT( --(Correspondence!$B$36:$B$999=admin!$B$15), --ISNUMBER(MATCH(Correspondence!$C$36:$C$999,ADMIN!B31:B35)), --(MONTH(Correspondence!A36:A999)=2))However, The formula counts the whole column B36:B54. This is a range that 'feeds' some other parts of the file; userform, worksheet cells and so on. Does this make a difference? If I used a range on the Correspondence sheet would that make a difference, as this column is fed from the ADMIN sheet, i.e. in cell A8 in correspondence the value is =ADMIN!B31.Any more ideas?Stu

 

[Aladin Akyurek]

Hello again,The criteria search doesn't seem to be working. I have put in my range where B31:B35 is what I want to count - =SUMPRODUCT( --(Correspondence!$B$36:$B$999=admin!$B$15), --ISNUMBER(MATCH(Correspondence!$C$36:$C$999,ADMIN!B31:B35)), --(MONTH(Correspondence!A36:A999)=2))However, The formula counts the whole column B36:B54. This is a range that 'feeds' some other parts of the file; userform, worksheet cells and so on. Does this make a difference? If I used a range on the Correspondence sheet would that make a difference, as this column is fed from the ADMIN sheet, i.e. in cell A8 in correspondence the value is =ADMIN!B31.Any more ideas?Stu

The match-type must be set to 0...

=SUMPRODUCT(
  --(Correspondence!$B$36:$B$999=admin!$B$15),
  --ISNUMBER(MATCH(Correspondence!$C$36:$C$999,ADMIN!B31:B35,0)),
  --(MONTH(Correspondence!A36:A999)=2))