SumIf Application, or Not?

[rcreek09]

I am trying to sum the table below based on the column headers. I want an input cell, "condition to sum", so I can enter a partial header and then sum accordingly. Example, sum all columns CONTAINING "A", or all columns CONTAINING "Weekly", etc.


The sumif works for only the first line of the sumrange. Have I lost my mind in thinking I can do this with sumif, or am I just missing something in the use of the formula?


I tried using sumproduct, which I really like. Problem is as soon as I think I understand sumproduct, I try to use it in a different way and, WRONG, apparently I do NOT understand it! I could not get it to work at all.


The sample data is below, but the headers are skewed. The link to the Google docs file is also included.


Thanks for any and all suggestions!


Deb

[pre]

A - Daily	A - Weekly	A - Monthly	A - Ad Hoc	O - Weekly	O - Monthly	O - Ad Hoc	M - Daily	M - Weekly	M - Monthly	M - Ad Hoc
1	11	13	12	20	1	7	12	16	3	8
12	13	6	20	14	9	19	10	12	7	18
11	6	14	20	12	2	4	16	15	13	10
11	3	15	1	16	9	2	5	9	2	3
2	9	1	10	12	12	15	9	2	4	8

sumif yields	37		SUMIF(A1:K1,B10,A2:K6)
should yield	191		SUM(A2:D6)
Condition to Sum	A*

[/pre]
https://docs.google.com/open?id=0ByeVouqBkD-KZ0Q3V3UxMXlwa2c

 

[Hui]

Rcreek09


Change cell B10 to A not A*

Then try:

=SUMPRODUCT(($A$2:$K$6)*(LEFT($A$1:$K$1,1)=$B$10))

Want to know how it works?

Have a read of: http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

 

[rcreek09]

I see how that works, but that defeats the purpose of my lookup cell. I would have to change my sumproduct formula every time I change the input. When I want to look up "weekly", I have to change the sumproduct to ....right(a1:k1,6). Same for Monthly, etc.


I can certainly do that, but it just seems it should work with putting the wildcard in the lookup cell.


I assume since you answered with sumproduct, that NO, I cannot achieve this with a sumif. So will mark that off the list and proceed with the sumproduct.


Thanks very much for your help.

 

[Hui]

Rcreek09


In C10: Type Weekly or Monthly or Ad Hoc or leave it blank

then use

=SUMPRODUCT(($A$2:$K$6)*(LEFT($A$1:$K$1,1)=$B$10)*(RIGHT($A$1:$K$1,LEN($C$10))=$C$10))

I wouldn't even attempt that with Sumif/s it just doesn't have the flexibility that Sumproduct has to conditionally work in 2 Dimensions

 

[NARAYANK991]

Hi ,


Try this :


=SUM(OFFSET($A$2:$K$6,,MATCH($B$10&"*",$A$1:$K$1,0)-1,5,COUNTIF($A$1:$K$1,$B$10&"*")))


where B10 can have just "A" or "O".


The above assumes that all the 'A's will be together.


Narayan

 

[rcreek09]

=SUMPRODUCT((ISNUMBER(FIND(B10,(A1:K1),1)))*A6:K6)


This seems to be working for all conditions! I do have to use a space with the A, O, or Ms but I can live with that.


Awesome!

 

[NARAYANK991]

Hi ,


A small correction :


=SUMPRODUCT((ISNUMBER(FIND(B10,(A1:K1),1)))*A2:K6)


Narayan

 

[rcreek09]

Gees, you don't miss a thing!


You are of course correct. I typed it in rather than pasting and I missed the A2!


Again, thanks so much. It is so nice to be able to get context specific, INTELLIGENT, timely help. This site is a lifesaver.


Have a great weekend.