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)
G-Man Says 'Reinstate Monica'
  • 7,678
  • 22
  • 41
  • 87
Nanda Prabhu
  • 3
  • 2
  • 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? – barry houdini Jul 14 '15 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. – Nanda Prabhu Jul 14 '15 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) – G-Man Says 'Reinstate Monica' Jul 14 '15 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) – G-Man Says 'Reinstate Monica' Jul 14 '15 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](http://superuser.com/q/889201/354511) and [here](http://superuser.com/q/892744/354511).  … (Cont’d) – G-Man Says 'Reinstate Monica' Jul 14 '15 at 19:36
  • (Cont’d) …  (5) What does “This formula is not working” mean?  Show us sample input data (ideally five to ten rows, rather than 100 or 10000), say what result you expect and what you are getting.  If you’ve gotten a simpler form of your formula working, show us that and explain how you’re trying to extend it.  Please do not respond in comments; [edit] your question to make it clearer.  (PS) If barry’s answer helps you, you should *accept* it by clicking on the check mark to the left. – G-Man Says 'Reinstate Monica' Jul 14 '15 at 19:36
  • Hi G-Man, Sorry, i am new here and thanks for the information. – Nanda Prabhu Jul 15 '15 at 09:38

1 Answers1

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

barry houdini
  • 10,892
  • 1
  • 20
  • 25