I am trying to create a formula to sum across a row with the following:

If any of the cells contain the text "ILL" and the number value is equal to
8 or less than 8 sum. Any ideas?

=sumproduct((a2:f2="ill")*(a2:f2<=8)*a2:f2)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sam G" <Sam wrote in message
...
I am trying to create a formula to sum across a row with the following:

If any of the cells contain the text "ILL" and the number value is equal
to
8 or less than 8 sum. Any ideas?

Sam G wrote:
I am trying to create a formula to sum across a row with the following:

If any of the cells contain the text "ILL" and the number value is equal to
8 or less than 8 sum. Any ideas?


Give some examples of the values you expect to find in your row and the results
you want.

Hi,
is ILL and the numbers in one row??

"Sam G" wrote:

I am trying to create a formula to sum across a row with the following:

If any of the cells contain the text "ILL" and the number value is equal to
8 or less than 8 sum. Any ideas?

If you're using Excel 2007, the SUMIFS function is what you want.

If row 1 has the text values to check, and row 2 has the values to sum (and
to check if they're less than or equal to 8), the formula would be
=SUMIFS(2:2,2:2,"<=8",1:1,"*ILL*")

--
Rob Jordan
Powered by Creative Laziness


"Sam G" wrote:

I am trying to create a formula to sum across a row with the following:

If any of the cells contain the text "ILL" and the number value is equal to
8 or less than 8 sum. Any ideas?

But if the cell content is "ill", it won't have a number value, will it?
Won't that give a #VALUE! error if any cell in the range contains "ill"?
--
David Biddulph

"Don Guillett" wrote in message
...
=sumproduct((a2:f2="ill")*(a2:f2<=8)*a2:f2)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software


"Sam G" <Sam wrote in message
...
I am trying to create a formula to sum across a row with the following:

If any of the cells contain the text "ILL" and the number value is equal
to
8 or less than 8 sum. Any ideas?

"Don Guillett" wrote:

=sumproduct((a2:f2="ill")*(a2:f2<=8)*a2:f2)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sam G" <Sam wrote in message
...
I am trying to create a formula to sum across a row with the following:

If any of the cells contain the text "ILL" and the number value is equal
to
8 or less than 8 sum. Any ideas?

Thanks Don.. David is correct it returned a #value error. Let me try again. What I am trying to accomplishis, let's say I have 31 cells on the same row that contain the following; I'll just use 3 cells for the example. cell A1="ILL 8" cell B1= "ILL 6.2" and cell C1 has "ILL 8". If these were totaled there would be 22.2 "ILL". That's what I am trying to do account for ill hours thoughout any given month.

Eduardo,

Yes the numbers and ILL are on the same row, actually in the same cell.
Cell A1 = ILL 8 and Cell B1=ILL 6.2 and so on for the whole month. I am
trying to get all cells in the row totaled.

Thanks

Sam

"Eduardo" wrote:

Hi,
is ILL and the numbers in one row??

"Sam G" wrote:

I am trying to create a formula to sum across a row with the following:

If any of the cells contain the text "ILL" and the number value is equal to
8 or less than 8 sum. Any ideas?

Array formula (commit with CTRL+SHIFT+ENTER):

=SUM(IF(LEFT(A1:AE1,3)="ILL",
IF(--MID(A1:AE1,FIND(" ",A1:AE1)+1,LEN(A1:AE1))<=8,
--MID(A1:AE1,FIND(" ",A1:AE1)+1,LEN(A1:AE1)))))

Sam G wrote:
Eduardo,

Yes the numbers and ILL are on the same row, actually in the same cell.
Cell A1 = ILL 8 and Cell B1=ILL 6.2 and so on for the whole month. I am
trying to get all cells in the row totaled.

Thanks

Sam

"Eduardo" wrote:

Hi,
is ILL and the numbers in one row??

"Sam G" wrote:

I am trying to create a formula to sum across a row with the following:

If any of the cells contain the text "ILL" and the number value is equal to
8 or less than 8 sum. Any ideas?

Please note that this is an array formula. An array formula can perform
multiple calculations and then return either a single result or multiple
results. Array formulas act on two or more sets of values known as array
arguments. Each array argument must have the same number of rows and columns.
You create array formulas in the same way that you create other formulas,
except you press CTRL+SHIFT+ENTER to enter the formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula}"

=SUM(IF(LEFT(A1:A20,3)="ill",IF(--SUBSTITUTE(A1:A20,"ILL",)<=8,--SUBSTITUTE(A1:A20,"ILL",))))

If this post helps click Yes
---------------
Jacob Skaria


"Sam G" wrote:

Eduardo,

Yes the numbers and ILL are on the same row, actually in the same cell.
Cell A1 = ILL 8 and Cell B1=ILL 6.2 and so on for the whole month. I am
trying to get all cells in the row totaled.

Thanks

Sam

"Eduardo" wrote:

Hi,
is ILL and the numbers in one row??

"Sam G" wrote:

I am trying to create a formula to sum across a row with the following:

If any of the cells contain the text "ILL" and the number value is equal to
8 or less than 8 sum. Any ideas?

When posting you should always present the full question. Try this macro for
row 1

Sub sumproducttextwithvalue()
Dim ms As Long
dim i as long
dim ms as double
On Error Resume Next
For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
If LCase(Left(Cells(1, i), 3)) = "ill" Then
ms = ms + Mid(Cells(1, i), 4, 5)
End If
Next i
MsgBox Format(ms, "00.00")
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sam G" wrote in message
...


"Don Guillett" wrote:

=sumproduct((a2:f2="ill")*(a2:f2<=8)*a2:f2)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sam G" <Sam wrote in message
...
I am trying to create a formula to sum across a row with the following:

If any of the cells contain the text "ILL" and the number value is
equal
to
8 or less than 8 sum. Any ideas?

Thanks Don.. David is correct it returned a #value error. Let me try
again. What I am trying to accomplishis, let's say I have 31 cells on
the same row that contain the following; I'll just use 3 cells for the
example. cell A1="ILL 8" cell B1= "ILL 6.2" and cell C1 has "ILL 8". If
these were totaled there would be 22.2 "ILL". That's what I am trying to
do account for ill hours thoughout any given month.