351 questions
0
votes
1
answer
47
views
How to use ARRAYFORMULA to count the values of each row
I made a sheet to use as a test checker. Each test covers different domains. I can count all correct answers using SUMPRODUCT, but when I sort items by domain, the formula stops working. What formula ...
- 1
0
votes
2
answers
80
views
Googlesheets SUMPRODUCT with position
PORTION
NETO
PROPORTION
db 05;db 34
34140
0,6;0,4
db 05
10000
1
db 03;db 04;db 05
10000
0,7;0,1;0,2
db 04;db 05
5000
0,4;0,6
I have a sample file like the above table.
I need a =SUMPRODUCT(A2:A="...
0
votes
2
answers
77
views
Calculate Tax Income with Excel Formula
I am trying to calculate the tax Payable for person A. Formula to calculate in Table 2, cell D12, and calculate from D10. Salary in D10 will change depending on user input.
I have used few formula but ...
- 35
1
vote
2
answers
65
views
Sum with multiple criteria and first matches only (excluding duplicates)
I am trying to sum column D for the first match with column A = "green" & column C = "CA", so excluding duplicates for the combinations in A:B. The total should be 200.
company
...
0
votes
1
answer
61
views
SUMPRODUCT formula confusing performance in conditional conditional formatting
I use this sumproduct formula to check value between 2 sheets (sheet1 and Stopwork sheet) and to change the row color if result TRUE
=SUMPRODUCT(('Stop Work'!$D$8:$D$500=$C10)*('Stop Work'!$K$8:$K$500=...
- 109
0
votes
0
answers
45
views
How can I make a SUMPRODUCT formula use only the values marked with a checkbox? [duplicate]
I have a list of colors in column C.
List of colors and combinations
Since I need to know how many times a word is mentioned in column C, I use this formula that I found in the internet
=SUMPRODUCT((...
- 3
0
votes
1
answer
104
views
Why is SUMPRODUCT not calculating correctly?
I'm trying to write a formula where it sums if the name is on both sheets and has a string of "Intake Completed" and "Yes"
Here is the sample date for each sheet.
Sheet1
Name
D
...
1
vote
1
answer
64
views
MS Excel: count if difference between two numbers is in a range, and a third number is within another range
Please could you help me with a tricky (in my book) Excel problem. I have a database of numbers, where I need to count how many cases there are where a subtraction of numbers falls within a range, and ...
-2
votes
2
answers
101
views
How do I lookup row labels, find a value in an array on that line, and report that specific column header?
enter image description here
See image of my simple table in excel for reference
I have a sporting event that a person is assigned a division: Which is the value in H3.
Lookup this division in the ...
- 3
0
votes
2
answers
68
views
Google Sheets: SUMPRODUCT Formula for Summing Income and Expenses by Month Across Multiple Sheets
I am a beginner in Google Sheets and I'm working on a workbook with three sheets: Income, Expenditure, and Summary. My goal is to create a summary sheet that automatically calculates the total income ...
1
vote
2
answers
189
views
Excel Sumproduct with multiple criteria lookup in two tables
For a report, I need to calculate the SUMPRODUCT() of amounts in a data table multiplied by a factor value determined using a lookup table, the factor value (and thus column) to use depends on ...
- 11
0
votes
1
answer
68
views
EXCEL Sumproduct COUNT 2 AND condition every nth COLUMN, counting blank and not blank
Good Day to all! as my title suggest I am having a problem with the sumproduct formula to get the result that for the following condition
Check and Count every 4th column range for NOT blank ...
2
votes
4
answers
120
views
Using SUMPRODUCT involving a column which contains empty values coming from an IF(...; ...; "")
Let's say we have this Excel sheet:
A column containing 1 ; 2 ; 3 (i.e. only 3 rows)
B column containing =IF(COUNT(A1)>0;1/A1^2;"") recopied in B1:B100. Therefore only 3 cells will be ...
- 46.7k
0
votes
0
answers
12
views
I am using =SUMPRODUCT(--(D12:V12<>E12:W12-1))+1-COUNTIF(D12:W12,"") to count consecutive dates. Need to not count dates more than 12 months old
I am using =SUMPRODUCT(--(D12:V12<>E12:W12-1))+1-COUNTIF(D12:W12,"") to count consecutive dates. Need to not count dates more than 12 months old. I am using =COUNTIF(D12:W12,">&...
1
vote
3
answers
189
views
Case sensitive distinct count from one column based on cell values in another column in EXCEL
Can you please help me with finding the count of distinct (case sensitive) products sold (Column B) per store (Column C) as per below data? Product_sold column has data from B2: B10. Store column has ...
- 11