| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Criteria
The function below works
Answer = .SumIfs(Range("B1:B100"),Range("A1:A100"),"300") Criteria Column Column To Sum £233.56 £5000 £266.89 £6000 £455.91 £8000 etc Answer = £6000 How would I write the criteria to sum entries where the adjacent value ends in 88p or more - I have tried many different ways using wils cards etc but none of them work. Thankyou in anticipation |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Criteria
Type this:
=SUM(IF(A1:A100-INT(A1:A100)0.88,B1:B100,0)) but use Ctrl + Shift + Enter rather than just enter after you've typed it. Sam "Roger" wrote: The function below works Answer = .SumIfs(Range("B1:B100"),Range("A1:A100"),"300") Criteria Column Column To Sum £233.56 £5000 £266.89 £6000 £455.91 £8000 etc Answer = £6000 How would I write the criteria to sum entries where the adjacent value ends in 88p or more - I have tried many different ways using wils cards etc but none of them work. Thankyou in anticipation |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Criteria
Hi Roger
Try the below....But what about whole numbers...eg: 266.00 Criteria Column Column To Sum $233.50 $5,000.00 $266.88 $6,000.00 $455.88 $8,000.00 =SUMPRODUCT(--(TEXT(MOD(A2:A4,1)*100,"00")+0=88),B2:B4) If this post helps click Yes --------------- Jacob Skaria "Roger" wrote: The function below works Answer = .SumIfs(Range("B1:B100"),Range("A1:A100"),"300") Criteria Column Column To Sum £233.56 £5000 £266.89 £6000 £455.91 £8000 etc Answer = £6000 How would I write the criteria to sum entries where the adjacent value ends in 88p or more - I have tried many different ways using wils cards etc but none of them work. Thankyou in anticipation |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Criteria
If you mean the entries in Col A are =0.88 then try
=SUMPRODUCT(--(A2:A4=0.88),B2:B4) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi Roger Try the below....But what about whole numbers...eg: 266.00 Criteria Column Column To Sum $233.50 $5,000.00 $266.88 $6,000.00 $455.88 $8,000.00 =SUMPRODUCT(--(TEXT(MOD(A2:A4,1)*100,"00")+0=88),B2:B4) If this post helps click Yes --------------- Jacob Skaria "Roger" wrote: The function below works Answer = .SumIfs(Range("B1:B100"),Range("A1:A100"),"300") Criteria Column Column To Sum £233.56 £5000 £266.89 £6000 £455.91 £8000 etc Answer = £6000 How would I write the criteria to sum entries where the adjacent value ends in 88p or more - I have tried many different ways using wils cards etc but none of them work. Thankyou in anticipation |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Criteria
Thankyou for your suggestions - I will have a play
"Roger" wrote: The function below works Answer = .SumIfs(Range("B1:B100"),Range("A1:A100"),"300") Criteria Column Column To Sum £233.56 £5000 £266.89 £6000 £455.91 £8000 etc Answer = £6000 How would I write the criteria to sum entries where the adjacent value ends in 88p or more - I have tried many different ways using wils cards etc but none of them work. Thankyou in anticipation |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count how many criteria in a column match criteria in another colu | Excel Discussion (Misc queries) | |||
| Selection.AutoFilter Field / Criteria = criteria sometimes non-existing on worksheet | Excel Programming | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| have input box. Need criteria entered to refer to cond. format criteria | Excel Programming | |||
Linear Mode
