if statements using words

  • Thread starter Thread starter AdrenalinR
  • Start date Start date
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
 
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)
 
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

Back
Top