if statements using words
 

So I have a spread sheet with 4 columns Months and section are lists,
subsection is a list dependant on the choice from section.
A B C D
1 Month Section Subsection Cost
2
3
4
5

I then have a second sheet that I want to add up total costs based on Month
and subsection, much like below.

Jan Feb Mar Apr
Subsection 1 =
Subsection 2
Subsection 3


i thought an if statement with an if statment with a sumif in it would work
like this..

=IF(Sheet1!A2:A6="January",IF(SUMIF(Sheet1!c2:c6," Subsection
1",Sheet!D2:D6)0,SUMIF(Sheet1!c2:c6,"Subsectio n 1",Sheet!D2:D6),""),"")

But it doesn't and i think it's cause you can't have an if statement of
"IF(Sheet1!A2:A6="January"," or maybe there is even an easier way to write it
but in any case... i'm stumped and need help

if statements using words
 

Try the below formula. If you are getting the total for Jan-Subsection1
combination you need to change the text "Jan" and "Subsection1" in the
formula to suit your requirements.

=SUMPRODUCT((ISNUMBER(SEARCH("Jan",Sheet1!A2:A6,1) ))*ISNUMBER(SEARCH("SubSection1",Sheet1!C2:C6,1)), D2:D6)


'Text changed ..I have not tested this....Try this forumla in Sheet2 B2
'Also please make sure to name the headings and the entries in the same way..
=SUMPRODUCT((ISNUMBER(SEARCH(B$1,Sheet1!$A$2:$A$6, 1)))*ISNUMBER(SEARCH($A2,Sheet1!$C$2:$C$6,1)),$D$2 :$D$6)



--
If this post helps click Yes
---------------
Jacob Skaria


"AdrenalinR" wrote:

So I have a spread sheet with 4 columns Months and section are lists,
subsection is a list dependant on the choice from section.
A B C D
1 Month Section Subsection Cost
2
3
4
5

I then have a second sheet that I want to add up total costs based on Month
and subsection, much like below.

Jan Feb Mar Apr
Subsection 1 =
Subsection 2
Subsection 3


i thought an if statement with an if statment with a sumif in it would work
like this..

=IF(Sheet1!A2:A6="January",IF(SUMIF(Sheet1!c2:c6," Subsection
1",Sheet!D2:D6)0,SUMIF(Sheet1!c2:c6,"Subsectio n 1",Sheet!D2:D6),""),"")

But it doesn't and i think it's cause you can't have an if statement of
"IF(Sheet1!A2:A6="January"," or maybe there is even an easier way to write it
but in any case... i'm stumped and need help

if statements using words
 

No it just gave me a value of 0.

"Jacob Skaria" wrote:

if statements using words
 

With the below data in Sheet1 , can you try the below formula

A2 to D6
Jan|Section1|SubSection1|20
Jan|Section1|SubSection1|20
Jan|Section1|SubSection1|10
Feb|Section1|SubSection1|20
Feb|Section1|SubSection1|20

=SUMPRODUCT((ISNUMBER(SEARCH("Jan",Sheet1!A2:A6,1) ))*ISNUMBER(SEARCH("SubSection1",Sheet1!C2:C6,1)), D2:D6)


If this post helps click Yes
---------------
Jacob Skaria


"AdrenalinR" wrote:

No it just gave me a value of 0.

"Jacob Skaria" wrote: