Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return value based on two criteria
how do i get excel to give me the sum if two criteria are true, but i want it
to return a value of only one of the criteria. For example, column A is the text 1A, column B is number 1. i need a formula that says if test in column A="1A" AND column B="1", then return the total number of times that "1A" appears in column A. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return value based on two criteria
Something like:
=SUMPRODUCT((A1:A40="1a")*(B1:B40=1)) "blopreste3180" wrote in message ... how do i get excel to give me the sum if two criteria are true, but i want it to return a value of only one of the criteria. For example, column A is the text 1A, column B is number 1. i need a formula that says if test in column A="1A" AND column B="1", then return the total number of times that "1A" appears in column A. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return value based on two criteria
Hi,
Try the following array formula (Ctrl+Shift+Enter) =count(if((range1="1A")*(range2=1),range1)) Regards, Ashish Mathur "blopreste3180" wrote: how do i get excel to give me the sum if two criteria are true, but i want it to return a value of only one of the criteria. For example, column A is the text 1A, column B is number 1. i need a formula that says if test in column A="1A" AND column B="1", then return the total number of times that "1A" appears in column A. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return value based on two criteria
This did not work. It returned a value of "0" (zero). The formula should
reference my first range of B3:B3001 which, for example, will ="1" and my second range of GS3:GS3001, must also ="1", then I need the sum of ONLY the instance of "1" in B3:B3001 if BOTH of these criteria are met, otherwise, return Blank or zero. "Ashish Mathur" wrote: Hi, Try the following array formula (Ctrl+Shift+Enter) =count(if((range1="1A")*(range2=1),range1)) Regards, Ashish Mathur "blopreste3180" wrote: how do i get excel to give me the sum if two criteria are true, but i want it to return a value of only one of the criteria. For example, column A is the text 1A, column B is number 1. i need a formula that says if test in column A="1A" AND column B="1", then return the total number of times that "1A" appears in column A. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
get a total based on criteria in two columns | Excel Worksheet Functions | |||
Locate a cell, based on a criteria, then use the 'Cell' command... | Excel Discussion (Misc queries) | |||
Want to return a value based on a whether a number is within a ra. | Excel Worksheet Functions | |||
returning a value based on mulitple criteria | Excel Worksheet Functions | |||
Formula to determine a future date based on criteria | Excel Worksheet Functions |