SUM IF with Array

[Milind Keer]

Hi

I want to ADD numbers using multiple conditions.

Andy Mark MTD 10
YTD 20
CTD 30
Andy Mark MTD 40
YTD 50
CTD 60
Jhon Mark MTD 70
YTD 80
CTD 90



Now here i want to add numbers of D coloumn
If Coloumn 'A' has 'Andy' , Coloumn 'B' has 'Mark' and Coloumn 'C' has YTD
Rember A2, A3, A5, A6, B2, B3, B5 and B6 cells are blank. If I repeat Andy
and Mark in each row then its very easy to sum but i dont want to do that.

The formula should return the result (20 + 50) = 70

I want to add value for 'Andy' 'Mark' and 'YTD' and i dont want to repeat
andy and mark's name


Could you please let me know the solution.

Thanks
Milind

 

[Milind Keer]

oooppsss formatting issue


Andy---------Mark-------MTD-----10
<Blank>------<Blank>----YTD-----20
<Blank>-------<Blank>---CTD.........30
Andy--------Mark------- MTD-----40
<Blank>-----<Blank>----YTD------50
<Blank>-----<Blank>----CTD------60
Jhon---------Mark------- MTD------ 70
<Blank>-----<Blank>----YTD------- 80
<Blank>-----<Blank>----CTD------ 90


Now here i want to add numbers of D coloumn
If Coloumn 'A' has 'Andy' , Coloumn 'B' has 'Mark' and Coloumn 'C' has YTD
Rember A2, A3, A5, A6, B2, B3, B5 and B6 cells are blank. If I repeat Andy
and Mark in each row then its very easy to sum but i dont want to do that.

The formula should return the result (20 + 50) = 70

I want to add value for 'Andy' 'Mark' and 'YTD' and i dont want to repeat
andy and mark's name

Could you please let me know the solution.

Thanks
Milind

 

[Dave Peterson]

I would add those names to each row using the techniques at Debra Dalgleish's
site:
http://contextures.com/xlDataEntry02.html
or
http://www.contextures.com/xlVideos01.html#FillBlanks

Then hide the duplicates by using this:
http://contextures.com/xlCondFormat03.html#Duplicate

I think it makes things lots easier.

 

[Milind Keer]

Hi Dave

Thanks for the prompt reply. I did think of similar solution but the problem
is my data contains 1000s of rows and each time i get different sets of data
and this solution wont work for dat...

I can write a macro to dis... i mean i guess simple FOR LOOP would easily
take of dis.... but i just wondering can we do it using formulas...

thanks again!!
Milind

 

[Dave Peterson]

The code at Debra's site (using specialcells) would probably be faster than
looping through lots and lots of cells.

I don't know of any formula that would do what you want. I'd bet it would be
pretty complex if, er, when someone suggests one.

 

[Milind Keer]

Hi Dave

ya!! even i think its not that easy to write any fornula for below
condition...

Evry month i m getting different sets of data the overall template is same
but data and no of rows are different....

anywayz thanks a lot for your advise and lets wait for reply from some XL GURU

thanks again!!
Milind