| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif/SumProduct
Hi,
ok i have 2 columns.... Column A Column B 32 - 54 20 45 - 65 76 75 75 I need to add the number of times Column A and Column B both have values. SO in this case its 3 times. I'm not sure if i use count if or sumproduct and how. I tried: =SUMPRODUCT(--(G5:G15<""),--(H5:H15<"")) but its giving the wrong value. value. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif/SumProduct
=SUMPRODUCT(--(G5:G15<""),--(H5:H15<""))
That should work. See if this works: =SUMPRODUCT(--(ISNUMBER(G5:G15)),--(ISNUMBER(H5:H15))) -- Biff Microsoft Excel MVP "excelllllllll" wrote in message ... Hi, ok i have 2 columns.... Column A Column B 32 - 54 20 45 - 65 76 75 75 I need to add the number of times Column A and Column B both have values. SO in this case its 3 times. I'm not sure if i use count if or sumproduct and how. I tried: =SUMPRODUCT(--(G5:G15<""),--(H5:H15<"")) but its giving the wrong value. value. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif/SumProduct
You could try using ISNUMBER, eg:
=SUMPRODUCT((ISNUMBER(A5:A15))*(ISNUMBER(B5:B15))) Success? hit the YES below -- Max Singapore --- "excelllllllll" wrote: Column A Column B 32 - 54 20 45 - 65 76 75 75 I need to add the number of times Column A and Column B both have values. SO in this case its 3 times. I'm not sure if i use count if or sumproduct and how. I tried: =SUMPRODUCT(--(G5:G15<""),--(H5:H15<"")) but its giving the wrong value. value. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif/SumProduct
Hey I tried that and it still doesnt work. it counts all th numbers in
column A only. I need so it only counts when numbers appear in both columns only. "Max" wrote: You could try using ISNUMBER, eg: =SUMPRODUCT((ISNUMBER(A5:A15))*(ISNUMBER(B5:B15))) Success? hit the YES below -- Max Singapore --- "excelllllllll" wrote: Column A Column B 32 - 54 20 45 - 65 76 75 75 I need to add the number of times Column A and Column B both have values. SO in this case its 3 times. I'm not sure if i use count if or sumproduct and how. I tried: =SUMPRODUCT(--(G5:G15<""),--(H5:H15<"")) but its giving the wrong value. value. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif/SumProduct
Nevermind my previous post!
it totally worked!!! thanks! "Max" wrote: You could try using ISNUMBER, eg: =SUMPRODUCT((ISNUMBER(A5:A15))*(ISNUMBER(B5:B15))) Success? hit the YES below -- Max Singapore --- "excelllllllll" wrote: Column A Column B 32 - 54 20 45 - 65 76 75 75 I need to add the number of times Column A and Column B both have values. SO in this case its 3 times. I'm not sure if i use count if or sumproduct and how. I tried: =SUMPRODUCT(--(G5:G15<""),--(H5:H15<"")) but its giving the wrong value. value. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif OR Sumproduct- I NEED HELP | Excel Worksheet Functions | |||
| SUMPRODUCT/COUNTIF | Excel Worksheet Functions | |||
| Countif or Sumproduct | Excel Worksheet Functions | |||
| COUNTIF or SUMPRODUCT | Excel Discussion (Misc queries) | |||
| SUMPRODUCT & COUNTIF | Excel Worksheet Functions | |||
Linear Mode
