| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need two different data types to give me a total
I'm not an Excel expert, but I'm pretty sure there's a formula for what I
need to obtain a total. I need to determine what my total (count?) would be if d2:d30="x" and g2:g30=not blank. Can anyone help me determine the formula to get this total? Thanks in advance. -- twc |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need two different data types to give me a total
Hi,
Try this =sumproduct(($D$2:$D$30="x")*($G$2:$G$30<"")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "silk723" wrote in message ... I'm not an Excel expert, but I'm pretty sure there's a formula for what I need to obtain a total. I need to determine what my total (count?) would be if d2:d30="x" and g2:g30=not blank. Can anyone help me determine the formula to get this total? Thanks in advance. -- twc |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need two different data types to give me a total
Try one of these...
Excel 2007 only: =COUNTIFS(D2:D30,"x",G2:G30,"<") Any version of Excel: =SUMPRODUCT(--(D2:D30="x"),--(G2:G30<"")) -- Biff Microsoft Excel MVP "silk723" wrote in message ... I'm not an Excel expert, but I'm pretty sure there's a formula for what I need to obtain a total. I need to determine what my total (count?) would be if d2:d30="x" and g2:g30=not blank. Can anyone help me determine the formula to get this total? Thanks in advance. -- twc |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need two different data types to give me a total
Perfect!!! Thank you very much!
-- twc "Ashish Mathur" wrote: Hi, Try this =sumproduct(($D$2:$D$30="x")*($G$2:$G$30<"")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "silk723" wrote in message ... I'm not an Excel expert, but I'm pretty sure there's a formula for what I need to obtain a total. I need to determine what my total (count?) would be if d2:d30="x" and g2:g30=not blank. Can anyone help me determine the formula to get this total? Thanks in advance. -- twc |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need two different data types to give me a total
You are welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "silk723" wrote in message ... Perfect!!! Thank you very much! -- twc "Ashish Mathur" wrote: Hi, Try this =sumproduct(($D$2:$D$30="x")*($G$2:$G$30<"")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "silk723" wrote in message ... I'm not an Excel expert, but I'm pretty sure there's a formula for what I need to obtain a total. I need to determine what my total (count?) would be if d2:d30="x" and g2:g30=not blank. Can anyone help me determine the formula to get this total? Thanks in advance. -- twc |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need two different data types to give me a total
I spoke too soon... :-(
The formula that Ashish provided worked, but there is a teeny problem - it's giving me the total, minus one. My total should be 34, it's coming out 33. I've checked all cells to make sure something wasn't hiding that I didn't see, but nothing was there. Any idea as to what might cause the undercount? Here's my formula: ="Total Number of Systems: "&SUMPRODUCT(($D$4:$D$58="ü")*($G$4:$G$58<"" )) -- twc "T. Valko" wrote: Try one of these... Excel 2007 only: =COUNTIFS(D2:D30,"x",G2:G30,"<") Any version of Excel: =SUMPRODUCT(--(D2:D30="x"),--(G2:G30<"")) -- Biff Microsoft Excel MVP "silk723" wrote in message ... I'm not an Excel expert, but I'm pretty sure there's a formula for what I need to obtain a total. I need to determine what my total (count?) would be if d2:d30="x" and g2:g30=not blank. Can anyone help me determine the formula to get this total? Thanks in advance. -- twc . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need two different data types to give me a total
Ignore the question...I found the problem. The wetware can't count... :-)
-- twc "silk723" wrote: I spoke too soon... :-( The formula that Ashish provided worked, but there is a teeny problem - it's giving me the total, minus one. My total should be 34, it's coming out 33. I've checked all cells to make sure something wasn't hiding that I didn't see, but nothing was there. Any idea as to what might cause the undercount? Here's my formula: ="Total Number of Systems: "&SUMPRODUCT(($D$4:$D$58="ü")*($G$4:$G$58<"" )) -- twc "T. Valko" wrote: Try one of these... Excel 2007 only: =COUNTIFS(D2:D30,"x",G2:G30,"<") Any version of Excel: =SUMPRODUCT(--(D2:D30="x"),--(G2:G30<"")) -- Biff Microsoft Excel MVP "silk723" wrote in message ... I'm not an Excel expert, but I'm pretty sure there's a formula for what I need to obtain a total. I need to determine what my total (count?) would be if d2:d30="x" and g2:g30=not blank. Can anyone help me determine the formula to get this total? Thanks in advance. -- twc . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Function to add 3 column (of YES) to give a total % complete | Excel Worksheet Functions | |||
| Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" | Charts and Charting in Excel | |||
| If the total = between 17-20 give it a score of 5, and so on | Excel Discussion (Misc queries) | |||
| want a formula give total quanitity break up frm other sheet | Excel Worksheet Functions | |||
| calculate which cells in column A will give me the total of column | Excel Worksheet Functions | |||
Linear Mode
