1

Afternoon everyone! Strap in as this might get a bit hectic!

I'm working on organizing sheets that are sent to me, and I don't want to change anything about the original sheets (A23:O). The sheet below is what it looks like and what I'll be referencing

enter image description here

What I need (In basic terms): I'm trying to calculate the sum of all the "Qty" (O24:O) ONLY IF the the text within "Description" (N24:N) text starts with a 'W' and if the "Department" (K24:K) has the value of "445" or "450".

What I've done so far: I was able to sum the "Qty" of "Department" '445' and '450' (B2) by utilizing this function:

=SUMIF(K24:K,"=445",O24:O) +SUMIF(K24:K,"=450",O24:O)

for the "Womens" total (B3) I've tried using the function below, but the issue I've run into is that it doesn't sum "Qty" as it only takes the sum of the "Description" if the texts starts with a 'W' and if the "Department" has a value of '445' or '450'.

=COUNTIFS(N24:N,"W *",K24:K,"=445")+COUNTIFS(N24:N,"W *",K24:K,"=450")

I've tried a couple different functions with no luck and I'm hoping someone has a solution. I want to thank anybody in advance for a solution and I greatly appreciate it.

Improve this question
1

1 Answer 1

Reset to default
1

Use filter(), like this:

=sum( iferror( filter( O24:O, left(N24:N) = "W", (K24:K = 445) + (K24:K = 450) ) ) )

When the values in column K are not numbers but text strings that look like numbers, use text strings:

=sum( iferror( filter( O24:O, left(N24:N) = "W", (K24:K = "445") + (K24:K = "450") ) ) )

When you have trouble determining whether the values in column K are numbers or text strings that look like numbers, use trim():

=sum( iferror( filter( O24:O, left(N24:N) = "W", (trim(K24:K) = "445") + (trim(K24:K) = "450") ) ) )

Improve this answer
5
  • Unfortunately, the functions given didn't work. Result is 0
    – Deklin
    Commented May 31, 2022 at 9:10
  • Chances are that the zero result indicates one of the following: (1) no value in column N starts with "W" (2) there are no rows with department 445 or 450, or (3) the values in column O are not numbers but text strings that look like numbers. Consider sharing a publicly editable sample spreadsheet with realistic-looking data.
    – doubleunary
    Commented May 31, 2022 at 9:21
  • Thank for the quick response! (1) Can be ruled out as there are precisely 44 rows on column 'N' that start with "W". Verified using the filter tab, highlighting, and checking the 'count' at the bottom right of the page. (2) Can be ruled out as there are 181 rows with departments '445' and '450' (with a total sum of 298 utilizing "Qty" from the first function as seen on B2). Verified using the filter tab, highlighting, and checking the 'count' at the bottom right of the page. (3) Could be extremely likely that but according to the function on B2, it does recognize it as them as values
    – Deklin
    Commented May 31, 2022 at 9:51
  • docs.google.com/spreadsheets/d/… Here is a sample sheet. Let me know if that helps
    – Deklin
    Commented May 31, 2022 at 10:11
  • The values in column K are not numbers but text strings that look like numbers. Edited the answer.
    – doubleunary
    Commented May 31, 2022 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.