conditional sum on multiple criteria with only 1st occurance

[igsboston]

Hello, I am trying to find a formula that will conditionally sum a column of data (i.e. a sumif) based on whether the data is associated with (across from) a cell that matches a list of criteria from another tab. I've used a sumproduct formula that i've found elsewhere on another post.

The current formula reads:

=SUMPRODUCT(--(ISNUMBER(MATCH('Lookup Sheet'!$B1:$B1000,Criteria!A2:A14,0))),'Lookup Sheet'!$D1:$D1000)

My criteria are in a list A2:A14, the column to search in is in column B and with the associated data i'm looking to sum is in column D on 'Lookup Sheet'.

The catch is i'd only like the formula to sum if it finds a match to one of the criteria if its the 1st occurance of that criteria. So right now the formula i'm using returns double the answer i'm looking for for certain criteria matches, e.g. "landscaping" because it occurs twice in the list i'm searching through.

I've attached an example of what i'm trying to do with the current formula in place on the "criteria" tab on another post. Link below. The data i'm trying to conditionally sum based on potential matches to the multiple criteria is on the tab called look-up sheet.


http://www.excelforum.com/excel-gen...eria-with-only-1st-occurance.html#post2439611

Many thanks,
Eric

 

[Aladin Akyurek]

Hello, I am trying to find a formula that will conditionally sum a column of data (i.e. a sumif) based on whether the data is associated with (across from) a cell that matches a list of criteria from another tab. I've used a sumproduct formula that i've found elsewhere on another post.

The current formula reads:

=SUMPRODUCT(--(ISNUMBER(MATCH('Lookup Sheet'!$B1:$B1000,Criteria!A2:A14,0))),'Lookup Sheet'!$D1:$D1000)

My criteria are in a list A2:A14, the column to search in is in column B and with the associated data i'm looking to sum is in column D on 'Lookup Sheet'.

The catch is i'd only like the formula to sum if it finds a match to one of the criteria if its the 1st occurance of that criteria. So right now the formula i'm using returns double the answer i'm looking for for certain criteria matches, e.g. "landscaping" because it occurs twice in the list i'm searching through.

I've attached an example of what i'm trying to do with the current formula in place on the "criteria" tab on another post. Link below. The data i'm trying to conditionally sum based on potential matches to the multiple criteria is on the tab called look-up sheet.


http://www.excelforum.com/excel-gen...eria-with-only-1st-occurance.html#post2439611

Many thanks,
Eric

LS stands for your 'Lookup Sheet' in what follows...

A17, control+shift+enter, not just enter, and copy across:

=SUM(IF(ISNUMBER(MATCH('Lookup Sheet'!$B$1:$B$12,A2:A14,0)),
    'Lookup Sheet'!$D$1:$D$12/(COUNTIF('Lookup Sheet'!$B$1:$B$12,
      'Lookup Sheet'!$B$1:$B$12))))

 

[igsboston]

Thanks Aladin, so you're using the array version of sum(if(, then dividing it by the count. Clever - trying it now. Another question, any way to do this without array formulas? I'm going to be using it eventually with many indirect() sheet references on a fairly large database and i'm worried it will slow things down pretty significantly.

 

[Aladin Akyurek]

Thanks Aladin, so you're using the array version of sum(if(, then dividing it by the count. Clever - trying it now.

Yeah, it expoits the idea of distributing the values associated with
types over their tokens.

Another question, any way to do this without array formulas? I'm going to be using it eventually with many indirect() sheet references on a fairly large database and i'm worried it will slow things down pretty significantly.

Yes, the formula would require significant processor time.

You might want to try the following in comparison.

Extend the data on Lookup Sheet with, say, in column E...

=D1/COUNTIF($B$1:$B$12,B1)

Now invoke in A17 and copy across:

=SUMPRODUCT(SUMIF('Lookup Sheet'!$B$1:$B$12,A2:A14,'Lookup Sheet'!$E$1:$E$12))

 

[igsboston]

Great idea Aladin, only issue is when i apply the formula later in the project, i'm going to have >1500 sheets similar to "lookup" sheet, would there be any way to apply the Dcount formula to all 1500 sheets at once?

Incidentally they are named in ascending order in the real database from 1 to 1500 which is why i'm going to be able to use an indirect formula to reference them within the sum product or sum(if formula i eventually use.

Does this make sense?

 

[Aladin Akyurek]

Great idea Aladin, only issue is when i apply the formula later in the project, i'm going to have >1500 sheets similar to "lookup" sheet, would there be any way to apply the Dcount formula to all 1500 sheets at once?

Not sure that's feasible

Incidentally they are named in ascending order in the real database from 1 to 1500 which is why i'm going to be able to use an indirect formula to reference them within the sum product or sum(if formula i eventually use.

Does this make sense?

If the number of formulas is not too large, it should be doable.