help with changing sumif to sumproduct

[bobh]

Hi,

In ExcelXP this sumif formula is not working and so I thought to try
and change it to a sumproduct but I have been un-successful in several
attempts to reconstruct it :-( The issue is the sunif will not sum
the values in more than one column as in I need the values in columns
N and O summed and the values in columns R and S summed.

=(SUMIF('PIP '!$A$9:$A$150,"Jan",'PIP '!$N$9:$O$150))/(SUMIF('PIP '!
$A$9:$A$150,"Jan",'PIP '!$R$9:$S$150))

thanks
bobh.

 

[joeu2004]

In ExcelXP this sumif formula is not working and so I thought to try
and change it to a sumproduct but I have been un-successful in several
attempts to reconstruct it  :-(  The issue is the sunif will not sum
the values in more than one column as in I need the values in columns
N and O summed and the values in columns R and S summed.
=(SUMIF('PIP  '!$A$9:$A$150,"Jan",'PIP  '!$N$9:$O$150))/(SUMIF('PIP '!
$A$9:$A$150,"Jan",'PIP  '!$R$9:$S$150))

Since you offer little insight into the problem that formula solves
and the situation, I cannot comment on whether or not what you are
trying to do is the right thing. But I wonder if this does what you
intended:

=(SUMIF('PIP '!$A$9:$A$150,"Jan",'PIP '!$N$9:$N$150)
+SUMIF('PIP '!$A$9:$A$150,"Jan",'PIP '!$O$9:$O$150))
/(SUMIF('PIP '!$A$9:$A$150,"Jan",'PIP '!$R$9:$R$150)
+SUMIF('PIP '!$A$9:$A$150,"Jan",'PIP '!$S$9:$S$150)

I am suspicious of the reference to the string "Jan".

If some cells A9:A150 contain only the text string "Jan", your formula
might be fine.

But if that is a month name and if A9:A150 contains dates that are
formatted so that the month name is displayed, you cannot compare
against the month name, even though you see it in the cell as a result
of the format.

If the above solution, (SUMIF+SUMIF)/(SUMIF+SUMIF), does not solve you
problem, I suggest that post a response with more detail about what is
in the cells: values and formats.

Even better.... For broader participation, you might want to post
future inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

 

[bobh]

Since you offer little insight into the problem that formula solves
and the situation, I cannot comment on whether or not what you are
trying to do is the right thing.  But I wonder if this does what you
intended:

=(SUMIF('PIP  '!$A$9:$A$150,"Jan",'PIP  '!$N$9:$N$150)
+SUMIF('PIP  '!$A$9:$A$150,"Jan",'PIP  '!$O$9:$O$150))
/(SUMIF('PIP  '!$A$9:$A$150,"Jan",'PIP  '!$R$9:$R$150)
+SUMIF('PIP  '!$A$9:$A$150,"Jan",'PIP  '!$S$9:$S$150)

I am suspicious of the reference to the string "Jan".

If some cells A9:A150 contain only the text string "Jan", your formula
might be fine.

But if that is a month name and if A9:A150 contains dates that are
formatted so that the month name is displayed, you cannot compare
against the month name, even though you see it in the cell as a result
of the format.

If the above solution, (SUMIF+SUMIF)/(SUMIF+SUMIF), does not solve you
problem, I suggest that post a response with more detail about what is
in the cells:  values and formats.

Even better....  For broader participation, you might want to post
future inquiries using the MS Answers Forums athttp://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum.  It's just that MS has ceased to
support the Usenet newsgroups.  Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

thanks for the rewrite and information.
A9:A150 is text and contains actual 3 character month descriptors ex:
Jan, Feb, Mar, etc...