0

I want to compare the month value (=TEXT(A1,"MMMMMM")) from sheet A with a cell value (JULY) in sheet B using an IF statement.

If the months are same then I need a sumproduct of the cells in sheet A.

This formula is not working:

=IF(Master!E7:E100=Sheet1!H1,(SUMPRODUCT((Master!F7:F100="Testing")*(Master!C7:C100="Bangalore"))),0)
7
  • It's hard to tell from your description and formula how your data is set up. Which cell contains the month, is that Sheet1!H1? What does the range 'Master Tracker'!E7:E10000 contain? Are they dates or text values with just the month? Jul 14, 2015 at 15:05
  • 1.The master tracker sheet contains the month and sheet 1 has the current month value in it. 2. Just months derived from the dates 3. Text values(JULY , August). How to use a range in IF statement i get what i want if i compare two cells but it shows error when i compare the range in IF Statement. Jul 14, 2015 at 15:11
  • (1) I edited your question.  Most of the changes were directed toward making your formula slightly shorter, and thus (hopefully) easier to understand.  (2) Please don’t talk about “sheet A” and “sheet B” in your narrative when your formula references Master and Sheet1.  Be consistent.  … (Cont’d) Jul 14, 2015 at 19:34
  • (Cont’d) …  (3) “I want to compare the month value (=TEXT(A1, "MMMMMM")) … with a cell value …”  What does this have to do with the rest of the question?  Why does your formula not contain =TEXT(…,"MMMMMM")?  Is that buried in the Master!E7:E100=Sheet1!H1?  If so, is that part of the problem?  Is that part working?  If the =TEXT(…, "MMMMMM") is working, I’m not sure it’s relevant to the question.  And, if it’s not working, then you need to make it clear, and not hide it.  … (Cont’d) Jul 14, 2015 at 19:35
  • (Cont’d) …  (4) A picture is worth a thousand words.  You cannot put a thousand words into a comment (in fact, there is a limit of 600 characters), so describing your data layout in comments is futile.  (I hate to say it, but your poor command of the English language makes this doubly true.)  Please show us (and also describe) your data layout in your question.  If you can’t post screenshots, type in representations of your sheets, as was done here and here.  … (Cont’d) Jul 14, 2015 at 19:36

1 Answer 1

0

If you want to count rows where all 3 conditions are satisfied - 1) the date matches, column F = "testing" and column C = "Bangalore" try COUNTIFS function

=COUNTIFS('Master Tracker'!E:E,Sheet1!H1,'Master Tracker'!F:F,"Testing",'Master Tracker'!C:C,"Bangalore")

1
  • Thanks a lot Barry...I was trying to do it with countif ... now It works ....Thanks again for the help Jul 15, 2015 at 4:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .