| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM with two conditions
I have been struggling with this for awhile, so I decided to post.
I am creating a spreadsheet with two sheets. One sheet lists courses we offer, while the second sheet lists textbooks. I want to write a formula in the first sheet that will count how many textbooks on the second sheet are assigned to a given course. In the second (textbook) sheet, I have a column that I put the courses to which are assigned each textbook. COLUMN A Includes "YES" if the textbook is still actively used or "NO" if it is discontinued. COLUMN B Title of textbook COLUMN T Courses (e.g., "PSY 250", "PSY 260, PSY 250", "") There are three possibilities: (1) No textbooks are assigned to that course. (No course IDs in Column T) (2) A textbook is assigned to just the one course. (Only one course ID in Column T) (3) A textbook is assigned to more than one course, including the course in question. (More than one course ID in Column T) So, I came up with this formula, which I typed in cell A22 of sheet 1: =SUM((Textbooks!$A$4:$A$119="YES")*(Textbooks!$T$4 :$T$119="*"&A22&"*")) What does this formula intended do? After I pressed "Shift"+"Control"+"Enter", it checks that two conditions are met for each row in the second sheet. First, the row must include "YES" in Column A and the row must include the course number in the current cell A22 (e.g., PSY 350). I needed to use wildcards cause in some cases, Column T may include "PSY 350", "BUS 280, PSY 350", "PSY 350, BUS 280", "PCC 344, PSY 350, BUS 280", etc. So, I wanted to count not only those just with PSY 350, but any other row that contains PSY 350. What's the problem? If I replace the wildcards and cell A22 and only use "PSY 350", it counts the PSY 350 instances, but does not include instances when PSY 350 is not the only course in a cell (e.g., "PSY 350, BUS 280"). It correctly finds only the PSY 350 in Column T when "YES" appears in Column A. I am sure I am using wildcards incorrectly. Any suggestions??? Thank you VERY much!!! Art |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM with two conditions
=SUM((Textbooks!$A$4:$A$119="YES")*(Textbooks!$T$ 4:$T$119="*"&A22&"*"))
Ther's only a few functions that support wildcards: SUMIF COUNTIF SEARCH MATCH HLOOKUP VLOOKUP Try this... Normally entered =SUMPRODUCT(--(Textbooks!$A$4:$A$119="YES"),--(ISNUMBER(SEARCH(A22,Textbooks!$T$4:$T$119)))) -- Biff Microsoft Excel MVP "Art" wrote in message ... I have been struggling with this for awhile, so I decided to post. I am creating a spreadsheet with two sheets. One sheet lists courses we offer, while the second sheet lists textbooks. I want to write a formula in the first sheet that will count how many textbooks on the second sheet are assigned to a given course. In the second (textbook) sheet, I have a column that I put the courses to which are assigned each textbook. COLUMN A Includes "YES" if the textbook is still actively used or "NO" if it is discontinued. COLUMN B Title of textbook COLUMN T Courses (e.g., "PSY 250", "PSY 260, PSY 250", "") There are three possibilities: (1) No textbooks are assigned to that course. (No course IDs in Column T) (2) A textbook is assigned to just the one course. (Only one course ID in Column T) (3) A textbook is assigned to more than one course, including the course in question. (More than one course ID in Column T) So, I came up with this formula, which I typed in cell A22 of sheet 1: =SUM((Textbooks!$A$4:$A$119="YES")*(Textbooks!$T$4 :$T$119="*"&A22&"*")) What does this formula intended do? After I pressed "Shift"+"Control"+"Enter", it checks that two conditions are met for each row in the second sheet. First, the row must include "YES" in Column A and the row must include the course number in the current cell A22 (e.g., PSY 350). I needed to use wildcards cause in some cases, Column T may include "PSY 350", "BUS 280, PSY 350", "PSY 350, BUS 280", "PCC 344, PSY 350, BUS 280", etc. So, I wanted to count not only those just with PSY 350, but any other row that contains PSY 350. What's the problem? If I replace the wildcards and cell A22 and only use "PSY 350", it counts the PSY 350 instances, but does not include instances when PSY 350 is not the only course in a cell (e.g., "PSY 350, BUS 280"). It correctly finds only the PSY 350 in Column T when "YES" appears in Column A. I am sure I am using wildcards incorrectly. Any suggestions??? Thank you VERY much!!! Art |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM with two conditions
Ther's only a few functions that support wildcards:
I forgot about Excel 2007. SUMIF - all versions of Excel COUNTIF - all versions of Excel SEARCH - all versions of Excel MATCH - all versions of Excel HLOOKUP - all versions of Excel VLOOKUP - all versions of Excel SUMIFS - Excel 2007 COUNTIFS - Excel 2007 This formula will work in any (modern) version of Excel: =SUMPRODUCT(--(Textbooks!$A$4:$A$119="YES"),--(ISNUMBER(SEARCH(A22,Textbooks!$T$4:$T$119)))) If you're using Excel 2007... =COUNTIFS(Textbooks!$A$4:$A$119,"YES",Textbooks!$T $4:$T$119,"*"&A22&"*") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... =SUM((Textbooks!$A$4:$A$119="YES")*(Textbooks!$T$ 4:$T$119="*"&A22&"*")) Ther's only a few functions that support wildcards: SUMIF COUNTIF SEARCH MATCH HLOOKUP VLOOKUP Try this... Normally entered =SUMPRODUCT(--(Textbooks!$A$4:$A$119="YES"),--(ISNUMBER(SEARCH(A22,Textbooks!$T$4:$T$119)))) -- Biff Microsoft Excel MVP "Art" wrote in message ... I have been struggling with this for awhile, so I decided to post. I am creating a spreadsheet with two sheets. One sheet lists courses we offer, while the second sheet lists textbooks. I want to write a formula in the first sheet that will count how many textbooks on the second sheet are assigned to a given course. In the second (textbook) sheet, I have a column that I put the courses to which are assigned each textbook. COLUMN A Includes "YES" if the textbook is still actively used or "NO" if it is discontinued. COLUMN B Title of textbook COLUMN T Courses (e.g., "PSY 250", "PSY 260, PSY 250", "") There are three possibilities: (1) No textbooks are assigned to that course. (No course IDs in Column T) (2) A textbook is assigned to just the one course. (Only one course ID in Column T) (3) A textbook is assigned to more than one course, including the course in question. (More than one course ID in Column T) So, I came up with this formula, which I typed in cell A22 of sheet 1: =SUM((Textbooks!$A$4:$A$119="YES")*(Textbooks!$T$4 :$T$119="*"&A22&"*")) What does this formula intended do? After I pressed "Shift"+"Control"+"Enter", it checks that two conditions are met for each row in the second sheet. First, the row must include "YES" in Column A and the row must include the course number in the current cell A22 (e.g., PSY 350). I needed to use wildcards cause in some cases, Column T may include "PSY 350", "BUS 280, PSY 350", "PSY 350, BUS 280", "PCC 344, PSY 350, BUS 280", etc. So, I wanted to count not only those just with PSY 350, but any other row that contains PSY 350. What's the problem? If I replace the wildcards and cell A22 and only use "PSY 350", it counts the PSY 350 instances, but does not include instances when PSY 350 is not the only course in a cell (e.g., "PSY 350, BUS 280"). It correctly finds only the PSY 350 in Column T when "YES" appears in Column A. I am sure I am using wildcards incorrectly. Any suggestions??? Thank you VERY much!!! Art |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM with two conditions
You're awesome...thanks!
"T. Valko" wrote: Ther's only a few functions that support wildcards: I forgot about Excel 2007. SUMIF - all versions of Excel COUNTIF - all versions of Excel SEARCH - all versions of Excel MATCH - all versions of Excel HLOOKUP - all versions of Excel VLOOKUP - all versions of Excel SUMIFS - Excel 2007 COUNTIFS - Excel 2007 This formula will work in any (modern) version of Excel: =SUMPRODUCT(--(Textbooks!$A$4:$A$119="YES"),--(ISNUMBER(SEARCH(A22,Textbooks!$T$4:$T$119)))) If you're using Excel 2007... =COUNTIFS(Textbooks!$A$4:$A$119,"YES",Textbooks!$T $4:$T$119,"*"&A22&"*") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... =SUM((Textbooks!$A$4:$A$119="YES")*(Textbooks!$T$ 4:$T$119="*"&A22&"*")) Ther's only a few functions that support wildcards: SUMIF COUNTIF SEARCH MATCH HLOOKUP VLOOKUP Try this... Normally entered =SUMPRODUCT(--(Textbooks!$A$4:$A$119="YES"),--(ISNUMBER(SEARCH(A22,Textbooks!$T$4:$T$119)))) -- Biff Microsoft Excel MVP "Art" wrote in message ... I have been struggling with this for awhile, so I decided to post. I am creating a spreadsheet with two sheets. One sheet lists courses we offer, while the second sheet lists textbooks. I want to write a formula in the first sheet that will count how many textbooks on the second sheet are assigned to a given course. In the second (textbook) sheet, I have a column that I put the courses to which are assigned each textbook. COLUMN A Includes "YES" if the textbook is still actively used or "NO" if it is discontinued. COLUMN B Title of textbook COLUMN T Courses (e.g., "PSY 250", "PSY 260, PSY 250", "") There are three possibilities: (1) No textbooks are assigned to that course. (No course IDs in Column T) (2) A textbook is assigned to just the one course. (Only one course ID in Column T) (3) A textbook is assigned to more than one course, including the course in question. (More than one course ID in Column T) So, I came up with this formula, which I typed in cell A22 of sheet 1: =SUM((Textbooks!$A$4:$A$119="YES")*(Textbooks!$T$4 :$T$119="*"&A22&"*")) What does this formula intended do? After I pressed "Shift"+"Control"+"Enter", it checks that two conditions are met for each row in the second sheet. First, the row must include "YES" in Column A and the row must include the course number in the current cell A22 (e.g., PSY 350). I needed to use wildcards cause in some cases, Column T may include "PSY 350", "BUS 280, PSY 350", "PSY 350, BUS 280", "PCC 344, PSY 350, BUS 280", etc. So, I wanted to count not only those just with PSY 350, but any other row that contains PSY 350. What's the problem? If I replace the wildcards and cell A22 and only use "PSY 350", it counts the PSY 350 instances, but does not include instances when PSY 350 is not the only course in a cell (e.g., "PSY 350, BUS 280"). It correctly finds only the PSY 350 in Column T when "YES" appears in Column A. I am sure I am using wildcards incorrectly. Any suggestions??? Thank you VERY much!!! Art . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM with two conditions
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Art" wrote in message ... You're awesome...thanks! "T. Valko" wrote: Ther's only a few functions that support wildcards: I forgot about Excel 2007. SUMIF - all versions of Excel COUNTIF - all versions of Excel SEARCH - all versions of Excel MATCH - all versions of Excel HLOOKUP - all versions of Excel VLOOKUP - all versions of Excel SUMIFS - Excel 2007 COUNTIFS - Excel 2007 This formula will work in any (modern) version of Excel: =SUMPRODUCT(--(Textbooks!$A$4:$A$119="YES"),--(ISNUMBER(SEARCH(A22,Textbooks!$T$4:$T$119)))) If you're using Excel 2007... =COUNTIFS(Textbooks!$A$4:$A$119,"YES",Textbooks!$T $4:$T$119,"*"&A22&"*") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... =SUM((Textbooks!$A$4:$A$119="YES")*(Textbooks!$T$ 4:$T$119="*"&A22&"*")) Ther's only a few functions that support wildcards: SUMIF COUNTIF SEARCH MATCH HLOOKUP VLOOKUP Try this... Normally entered =SUMPRODUCT(--(Textbooks!$A$4:$A$119="YES"),--(ISNUMBER(SEARCH(A22,Textbooks!$T$4:$T$119)))) -- Biff Microsoft Excel MVP "Art" wrote in message ... I have been struggling with this for awhile, so I decided to post. I am creating a spreadsheet with two sheets. One sheet lists courses we offer, while the second sheet lists textbooks. I want to write a formula in the first sheet that will count how many textbooks on the second sheet are assigned to a given course. In the second (textbook) sheet, I have a column that I put the courses to which are assigned each textbook. COLUMN A Includes "YES" if the textbook is still actively used or "NO" if it is discontinued. COLUMN B Title of textbook COLUMN T Courses (e.g., "PSY 250", "PSY 260, PSY 250", "") There are three possibilities: (1) No textbooks are assigned to that course. (No course IDs in Column T) (2) A textbook is assigned to just the one course. (Only one course ID in Column T) (3) A textbook is assigned to more than one course, including the course in question. (More than one course ID in Column T) So, I came up with this formula, which I typed in cell A22 of sheet 1: =SUM((Textbooks!$A$4:$A$119="YES")*(Textbooks!$T$4 :$T$119="*"&A22&"*")) What does this formula intended do? After I pressed "Shift"+"Control"+"Enter", it checks that two conditions are met for each row in the second sheet. First, the row must include "YES" in Column A and the row must include the course number in the current cell A22 (e.g., PSY 350). I needed to use wildcards cause in some cases, Column T may include "PSY 350", "BUS 280, PSY 350", "PSY 350, BUS 280", "PCC 344, PSY 350, BUS 280", etc. So, I wanted to count not only those just with PSY 350, but any other row that contains PSY 350. What's the problem? If I replace the wildcards and cell A22 and only use "PSY 350", it counts the PSY 350 instances, but does not include instances when PSY 350 is not the only course in a cell (e.g., "PSY 350, BUS 280"). It correctly finds only the PSY 350 in Column T when "YES" appears in Column A. I am sure I am using wildcards incorrectly. Any suggestions??? Thank you VERY much!!! Art . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditions | Excel Worksheet Functions | |||
| Countif Conditions - Use of conditions that vary by cell value | Excel Discussion (Misc queries) | |||
| 2 Conditions + Sum of a colum matching those conditions | Excel Worksheet Functions | |||
| shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
| How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
Linear Mode
