Problem displaying correct data.

Hi all,

Im still working on creating the same spreadsheet that I had a problem with yesterday, however another problem has arisen. I don't believe there's a way to attach a spreadsheet with the info as this is all highly sensitive and it's all tied together, so I''ll explain it the best I can.

I'm entering the following formula:

=SUM(IF(Terms!$I$2:$I$598=Roster!$T$15,IF(Terms!$G$2:$G$598=Roster!$O23,1,0),0))

What I'm looking for the sum of (Terminations) data for any given month (which is what column I in my terms worksheet references through the Roster entry T15, which is a drop down list for all the months in a year), as well as the reason for the termination (referenced from the term reason column G in the Termination worksheet, which matches with Roster entry O23). This gives me an absolute number of terminations for month "x" in regards to reason "y" (Ive only listed one here to keep it simple).

Here's the interesting part: It works some of the time. What I mean is, is when I select anything from january to august, it reports back some of the terminations but not all of them. For Sept and October it reports nothing at all. I've made sure all the formatting and entries are exact on both worksheets and still a no go. If anyone can help I'd appreciate it.

Originally Posted by VinceValdez12

Hi all,

Im still working on creating the same spreadsheet that I had a problem with yesterday, however another problem has arisen. I don't believe there's a way to attach a spreadsheet with the info as this is all highly sensitive and it's all tied together, so I''ll explain it the best I can.

I'm entering the following formula:

=SUM(IF(Terms!$I$2:$I$598=Roster!$T$15,IF(Terms!$G$2:$G$598=Roster!$O23,1,0),0))

What I'm looking for the sum of (Terminations) data for any given month (which is what column I in my terms worksheet references through the Roster entry T15, which is a drop down list for all the months in a year), as well as the reason for the termination (referenced from the term reason column G in the Termination worksheet, which matches with Roster entry O23). This gives me an absolute number of terminations for month "x" in regards to reason "y" (Ive only listed one here to keep it simple).

Here's the interesting part: It works some of the time. What I mean is, is when I select anything from january to august, it reports back some of the terminations but not all of them. For Sept and October it reports nothing at all. I've made sure all the formatting and entries are exact on both worksheets and still a no go. If anyone can help I'd appreciate it.

How are the dates entered in column I.... i.e. if you click on a date what does it look like in the formula bar? Also, where are the months in the drop down list coming from and how are those entered. Are they simply text entries like "January", "February", etc...?

Column I is a manual text entry and are spelled out in their entirity. The value in T15 is just a list of data containing the months in order.

**EDIT: I just tried copying the drop down data and making it identical in both places, and it still does the same thing.

Originally Posted by VinceValdez12

Column I is a manual text entry and are spelled out in their entirity. The value in T15 is just a list of data containing the months in order.

...And your sure there are no spaces any of the data?

Does this revision work?

=SUM(IF(TRIM(Terms!$I$2:$I$598)=TRIM(Roster!$T$15),IF(TRIM(Terms!$G $2:$G$598)=TRIM(Roster!$O23),1,0),0))

You know that formula must be confirmed with CTRL+SHIFT+ENTER to get it to work, right?

Also, does the cell that you are putting the result in have any formatting applied?

If these don't work, then without seeing the sheet, it's hard to say what might be wrong with it.

Here's the Sumproduct version of your formula. Does it make a difference?

Please Login or Register  to view this content.