if statements using words

A

AdrenalinR

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,"Subsection 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
 
J

Jacob Skaria

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)
 
J

Jacob Skaria

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top